How to convert numbers to year/month/day 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 “2 year 6 months 23 days” in a single cell?

First Date: 2nd March 2015
Second Date: 25th September 2017

Use formula: DATEDIF(FirstDate,Seconddate,”y”)&” Year” &DATEDIF(FirstDate,Seconddate,”ym”)&” Months “&DATEDIF(FirstDate,Seconddate,”md”)&” Days”

Understanding the formula:

  1. DATEDIF(FirstDate,Seconddate,”y”)–Calculates Years between the two dates
  2. DATEDIF(FirstDate,Seconddate,”ym”)–Calculates Months between the dates, as if the dates were in the same year.
  3. DATEDIF(FirstDate,Seconddate,”md”)–Calculates Days between the two dates, as if the dates were in the same month and year.
  4. And in between the above formulas we have used “&” operator to concatenate strings in Excel

Share with your friends & keep learning:)



Be the first to comment

Leave a Reply

Your email address will not be published.