Skip to main content

How to convert numbers to year/month/day or date in Excel?

We can easily count the days between two dates in Excel, but how to display the number of days as year/month/day, such as "1 year 4 months 25days"? And what if converting a number yyyymmdd to a normal date? This article is talking about converting numbers to year/month/day or dates in Excel.

Convert numbers (days) to year/month/day

This method will introduce a formula to convert the number of days to year/month/day such as "1 year 4 months 25days" in Excel. Please do as follows:

1. Select a blank cell you will output the conversion result, and enter the following formula into it, and press the Enter key.

=DATEDIF(0,A2,"y")&" years " &DATEDIF(0,A2,"ym")&" months "&DATEDIF(0,A2,"md")&" days"

Note: A2 is the cell with number of days that you will convert to year/month/day.

2. Keep selecting the cell of conversion result, and drag its Fill Handle to the range as you need. And then you will see all numbers are converted to year/month/day. See screenshot:

Only one click to remove all formulas but keep calculated values from multiple cells


Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Convert numbers (days) to year/month/day with an amazing tool

Some users may feel hard to memorize formulas to convert the number of days to year/month/days in Excel. Here I recommend the Formula Helper of Kutools for Excel to quickly finish the conversation without memorizing the painful formulas.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the cell you will put the calculation result at, and click Kutools > Formula Helper > Formula Helper to enable the feature.

2. In the Formulas Helper dialog, please select Date from the Formula Type drop-down list, click to highlight Convert days to year month day in the Choose a formula list box, then specify the number cell in the Number box, and finally click the Ok button.

Now the calculation result is output in the selected cell. If necessary, you can drag the AutoFill Handle of this cell to copy this formula to other cells.

Convert general numbers to date of default date formatting

Sometimes, you type or paste dates into Excel, but the dates show as 5-digits of general numbers. In this case, you can easily convert 5-digits of general numbers to dates with following methods:

Select the 5-digits numbers, and click Home > Number Format box > Short Date. And then you will see all selected 5-digits numbers are converted to dates with default date formatting at once.

Convert general numbers to date with custom date formatting

Kutools for Excel's Apply Date Formatting utility can help you quickly convert 5-digits numbers to dates with custom date formatting.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the 5-digits of numbers, and click Kutools > Format > Apply Date Formatting.

2. In the opening Apply Date Formatting dialog box, select the specified date formatting you will use in the Date formatting box, and click the Ok button. And then you will see all numbers are converted to dates with specified date formatting. See screenshot:

One click to convert number yyyymmdd to date

Sometimes, you may get some special numbers of yyyymmdd, such as 20151215. This section will show you an easy way with one click to convert these numbers of yyyymmdd to normal dates by the Convert to Date utility of Kutools for Excel.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

Select the cells with special numbers of yyyymmdd, and click Kutools > Content > Convert to Date. See screenshot:

And then you will see all selected numbers are concerted to normal dates. See screenshot:

Please click the Close button to close the Convert to Date dialog box.

Demo: convert numbers to date in Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Related articles:

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (24)
No ratings yet. Be the first to rate!
Kaan
Kaan
This comment was minimized by the moderator on the site
=YIL(B6)-YIL(A6) & " Yıl " & EĞER(AY(B6)>AY(A6);AY(B6)-AY(A6);AY(A6)-AY(B6)) & " Ay " & EĞER(GÜN(B6)>GÜN(A6);GÜN(B6)-GÜN(A6);GÜN(A6)-GÜN(B6)) & " Gün " bu şekil negatif işareti kaldırabilirsiniz:
Reply
0
0
Gurumoorthy
Gurumoorthy
This comment was minimized by the moderator on the site
Hi i have one doubt could you say please I want to return as 5.6 years in excel how i can write
Reply
0
0
Brent S.
Brent S.
This comment was minimized by the moderator on the site
I'm used the formula at the beginning of this topic to calculate how long my employees have worked so I can setup quarterly progress reviews with them. I have the number generated from a formula that gives me how many days from hire date until current date. What is the change in the formula to go from Hire date to the brake down of year/month/day?
Reply
0
0
Kay
Kay
This comment was minimized by the moderator on the site
In my situation, I wanted years and months if possible; if not, I wanted just months or just days. I ended up with:
=IF(cellcontainingdays="","",IF(DATEDIF(0,cellcontainingdays,"y")=0,"",CONCATENATE(DATEDIF(0,cellcontainingdays,"y")," years")) & IF(DATEDIF(0,cellcontainingdays,"ym")=0,"",IF(AND(DATEDIF(0,cellcontainingdays,"ym")<>0,DATEDIF(0,cellcontainingdays,"y")=0),CONCATENATE(DATEDIF(0,cellcontainingdays,"ym")," months"),CONCATENATE(", ",DATEDIF(0,cellcontainingdays,"ym")," months"))) & IF(DATEDIF(0,cellcontainingdays,"md")=0,"",IF(AND(DATEDIF(0,cellcontainingdays,"md")<>0,DATEDIF(0,cellcontainingdays,"y")=0,DATEDIF(0,cellcontainingdays,"ym")=0),CONCATENATE(DATEDIF(0,cellcontainingdays,"md")," days"),"")))
Reply
0
0
dreojs16
dreojs16
This comment was minimized by the moderator on the site
=IF(DATEDIF(0;A1;"y")=0;"";DATEDIF(0;A1;"y")&" year(s), ") & IF(DATEDIF(0;A1;"ym")=0;"";DATEDIF(0;A1;"ym")&" month(s), ") & IF(DATEDIF(0;A1;"md")=0;"";DATEDIF(0;A1;"md")&" day(s)")
Reply
Report
0
0
Ahmed
Ahmed
This comment was minimized by the moderator on the site
How to handle negative numbers?

it gives #NUM! error.
Reply
0
0
kellytte
kellytte     Ahmed
This comment was minimized by the moderator on the site
Hi Ahmed,

what about changing the negative numbers to positive with ABS function, and then adding minus sign before the whole formula?
Reply
Report
0
0
Imran
Imran
This comment was minimized by the moderator on the site
I love you guys.
Reply
0
0
Kevin Hall
Kevin Hall
This comment was minimized by the moderator on the site
Is there a way to automatically omit 0 values? For example, if the formula returns "0 years, 7 months, 16 days", is there a way for it to show up as "7 months, 16 days" instead?
Reply
0
0
DK
DK     Kevin Hall
This comment was minimized by the moderator on the site
=IFERROR(IF(DATEDIF(0,M2,"y")>0,DATEDIF(0,M2,"y")&" Years ","")&IF(DATEDIF(0,M2,"ym")>0,DATEDIF(0,M2,"ym")&" Months ","")&IF(DATEDIF(0,M2,"md")>0,DATEDIF(0,M2,"md")&" Days",""),"")
Reply
0
0
dreojs16
dreojs16     Kevin Hall
This comment was minimized by the moderator on the site
=IF(DATEDIF(0;A1;"y")=0;"";DATEDIF(0;A1;"y")&" year(s), ") & IF(DATEDIF(0;A1;"ym")=0;"";DATEDIF(0;A1;"ym")&" month(s), ") & IF(DATEDIF(0;A1;"md")=0;"";DATEDIF(0;A1;"md")&" day(s)")
Reply
Report
0
0
DK
DK     dreojs16
This comment was minimized by the moderator on the site
=IFERROR(IF(DATEDIF(0,M2,"y")>0,DATEDIF(0,M2,"y")&" Years ","")&IF(DATEDIF(0,M2,"ym")>0,DATEDIF(0,M2,"ym")&" Months ","")&IF(DATEDIF(0,M2,"md")>0,DATEDIF(0,M2,"md")&" Days",""),"")
Reply
0
0
Mahmud Parvez Uzzal
Mahmud Parvez Uzzal
This comment was minimized by the moderator on the site
=CONCATENATE(IF(INT(A2/365)>0,CONCATENATE(INT(A2/365)," Years "),""),CONCATEN ATE(IF(INT(MOD( A2,365)/30)>0,C ONCATENATE(INT( MOD(A2,365)/30) ," Months "),""),CONCATEN ATE(IF(MOD(MOD( A2,365),30)>0,C ONCATENATE(MOD( MOD(A2,365),30) ," Days "),""),))) My formula is ok, When you will copy the formula it will contain some space in word CONCATENATE Like C ONCATENATE/CONCATEN ATE. Please correct the word concatenate. Then i think it will work.
Reply
0
0
Omer Yousif
Omer Yousif
This comment was minimized by the moderator on the site
hello, thanks for the feedback but it's still not working, can you please send me your email so i can show you the table am doing ?! maybe some mistake is there. my email is . now my final table will contain number of days, like for example after the calculations the result will be (123) which is number of days so i want to convert it into ( year , month , days ). or if you have a formula to calculate the defferance between two dates and show the results as ( x year , x month , x days ) that would be super. waiting for your feedback and thanks.
Reply
0
0
dreojs16
dreojs16     Omer Yousif
This comment was minimized by the moderator on the site
=IF(DATEDIF(A1;A2;"y")=0;"";DATEDIF(A1;A2;"y")&" year(s), ") & IF(DATEDIF(A1;A2;"ym")=0;"";DATEDIF(A1;A2;"ym")&" month(s), ") & IF(DATEDIF(A1;A2;"md")=0;"";DATEDIF(A1;A2;"md")&" day(s)")
Reply
Report
0
0
dreojs16
dreojs16     dreojs16
This comment was minimized by the moderator on the site
change every ; to ,
Reply
Report
0
0
Raven momo
Raven momo     Omer Yousif
This comment was minimized by the moderator on the site
And what table is that ,that you say your doing?
Reply
0
0
SUM1
SUM1
This comment was minimized by the moderator on the site
To remove "0 years", "0 months" and "0 days": =IF(DATEDIF(C9,D9,"y")=0,"",DATEDIF(C9,D9,"y")&" year"&IF(DATEDIF(C9,D9,"y")1,"s",""))&" "&IF(DATEDIF(C9,D9,"ym")=0,"", DATEDIF(C9,D9,"ym")&" month"&IF(DATEDIF(C9,D9,"ym")1,"s",""))&" "&IF(DATEDIF(C9,D9,"md")=0,"", DATEDIF(C9,D9,"md")&" day"&IF(DATEDIF(C9,D9,"md")1,"s",""))
Reply
0
0
Omer Yousif
Omer Yousif
This comment was minimized by the moderator on the site
hello , i tried all those but still it's not working and showing formula error . please help i want to convert number of days to year, month , day ( like 365 days ) to ( 1 year , 0 month , 0 days ).
Reply
0
0
Omer Yousif
Omer Yousif
This comment was minimized by the moderator on the site
hkj.jh.jjli hh;o;yuy piyhkjhliji iuoi;io;oioio uh;oiio;oiij; iuj'oij;oij;pijioj'ioj'oijioj;iooj;o
Reply
0
0
Omer Yousif
Omer Yousif
This comment was minimized by the moderator on the site
Hello and greetings, actually I have tried all those but still, it's showing an error ( the formula you typed contains an error ). what to do now ?! please help, am trying to do some calculation formula to help me finish my work faster, most of it I finished remaining only is that I have to transfer the number of days ( like 365 ) to ( 1 year, 0 months, 0 days ) and so on. you can also contact me through my email ( ). thanks
Reply
0
0
MAHMUD PARVEZ UZZAL
MAHMUD PARVEZ UZZAL
This comment was minimized by the moderator on the site
Try with this =CONCATENATE(IF(INT(A2/365)>0,CONCATENATE(INT(A2/365)," Years "),""),CONCATENATE(IF(INT(MOD(A2,365)/30)>0,CONCATENATE(INT(MOD(A2,365)/30)," Months "),""),CONCATENATE(IF(MOD(MOD(A2,365),30)>0,CONCATENATE(MOD(MOD(A2,365),30)," Days "),""),)))
Reply
0
0
IAM1234
IAM1234
This comment was minimized by the moderator on the site
thanks but it is showing 0 years, 0 months, 0 days. i need like below. 28: 28 days 367: 1 year 2 days
Reply
0
0
Mahmud Parvez Uzzal
Mahmud Parvez Uzzal
This comment was minimized by the moderator on the site
If you consider 30 day 1 month and 365 days 1 year then you can use this formula =CONCATENATE(INT(A1/365)," Years ", INT(MOD(A1,365)/30)," Months ", MOD(MOD(A1,365),30)," Days ")
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations