Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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   

Convert general numbers to dates   

Convert general numbers to date with custom date formatting  

Convert number yyyymmdd to date   

One click to convert multiple non-standard formatting dates/numbers/text to normal dates in Excel

Kutools for Excel's Convert to Date utility can help you easily identify and convert non-standard dates or numbers (yyyymmdd) or text to normal dates with only one click in Excel. Full Feature Free Trial 60-day!
ad convert to date 1


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:

Select a blank cell you will output the conversion result, and enter the formula =DATEDIF(0,A2,"y")&" years " &DATEDIF(0,A2,"ym")&" months "&DATEDIF(0,A2,"md")&" days"  (A2 is the cell with number of days that you will convert to year/month/day) into it, and press the Enter key.
doc convert number to date year month day 1

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

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:
doc convert number to date year month day 2

Note: Only one click to remove formulas but keep calculated values from multiple cells with Kutools for Excel's To Actual utility!     Free Trial!
ad to actual 2


Convert general numbers to date

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:

Convert general numbers to date of default date formatting

Select the 5-digits numbers, and click Home > Number Format box > Short Date. See screenshot:
doc convert number to date year month day 3

And then you will see all selected 5-digits numbers are converted to dates with default date formatting as below screenshot shown:


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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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:
doc convert number to date year month day 6

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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:
doc convert number to date year month day 8

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

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Demo: convert numbers to date in Excel

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Related articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Imran · 4 months ago
    I love you guys.
  • To post as a guest, your comment is unpublished.
    Kevin Hall · 4 months ago
    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?
  • To post as a guest, your comment is unpublished.
    Mahmud Parvez Uzzal · 1 years ago
    =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.
  • To post as a guest, your comment is unpublished.
    Omer Yousif · 1 years ago
    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 omeryousif7@gmail.com. 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.
  • To post as a guest, your comment is unpublished.
    SUM1 · 1 years ago
    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",""))