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 30-day!
ad convert to date 1

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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.

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 tools for Excel. Full feature free trial 30-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.

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 tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


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:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 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!
officetab bottom
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.
    S Hoogewerf · 29 days ago
    =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)")
  • To post as a guest, your comment is unpublished.
    Ahmed · 1 months ago
    How to handle negative numbers?

    it gives #NUM! error.
    • To post as a guest, your comment is unpublished.
      kellytte · 13 days ago
      Hi Ahmed,

      what about changing the negative numbers to positive with ABS function, and then adding minus sign before the whole formula?
  • To post as a guest, your comment is unpublished.
    Imran · 11 months ago
    I love you guys.
  • To post as a guest, your comment is unpublished.
    Kevin Hall · 11 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.
      S Hoogewerf · 29 days ago
      =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)")
  • To post as a guest, your comment is unpublished.
    Mahmud Parvez Uzzal · 2 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 · 2 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.
      S Hoogewerf · 29 days ago
      =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)")
    • To post as a guest, your comment is unpublished.
      Raven momo · 2 years ago
      And what table is that ,that you say your doing?
  • To post as a guest, your comment is unpublished.
    SUM1 · 2 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",""))
  • To post as a guest, your comment is unpublished.
    Omer Yousif · 2 years ago
    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 ).
  • To post as a guest, your comment is unpublished.
    Omer Yousif · 2 years ago
    hkj.jh.jjli hh;o;yuy piyhkjhliji iuoi;io;oioio uh;oiio;oiij; iuj'oij;oij;pijioj'ioj'oijioj;iooj;o
  • To post as a guest, your comment is unpublished.
    Omer Yousif · 2 years ago
    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 ( omeryousif7@gmail.com ). thanks
  • To post as a guest, your comment is unpublished.
    MAHMUD PARVEZ UZZAL · 2 years ago
    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 "),""),)))
  • To post as a guest, your comment is unpublished.
    IAM1234 · 2 years ago
    thanks but it is showing 0 years, 0 months, 0 days. i need like below.

    28: 28 days
    367: 1 year 2 days
  • To post as a guest, your comment is unpublished.
    Mahmud Parvez Uzzal · 2 years ago
    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 ")