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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download 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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download 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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download 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: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or 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  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
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 Toolsets12 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, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
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:
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
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?
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"),"")))
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)")
This comment was minimized by the moderator on the site
How to handle negative numbers?

it gives #NUM! error.
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?
This comment was minimized by the moderator on the site
I love you guys.
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?
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",""),"")
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)")
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",""),"")
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.
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.
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)")
This comment was minimized by the moderator on the site
change every ; to ,
This comment was minimized by the moderator on the site
And what table is that ,that you say your doing?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations