Home
How to Convert Number of Days to Years Months and Days

How to Convert Number of Days to Years Months and Days

Excel Formulas 4 Comments

If you have the number of days and wonder what would be the years and months, you can use the following formula:

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

Where A2 is the cell with the number of days.

  • DATEDIF(0,A2,"y") returns the number of years;
  • DATEDIF(0,A2,"ym") returns the number of months; and
  • DATEDIF(0,A2,"md") returns the number of days.

When the number of years, months or days returns 1, the above formula returns 1 years (or months, days) and that is grammarly not correct. Please use the IF Function to change from plural to singular.

=DATEDIF(0,A2, "y") & IF(DATEDIF(0,A2, "y")=1, " year ", " years ") &DATEDIF(0,A2,"ym") & IF(DATEDIF(0,A2, "ym")=1, " month ", " months ") & DATEDIF(0,A2, "md") & IF(DATEDIF(0,A2, "md")=1, " day"," days")

4 Comments

  1. Elease Houston June 16, 2021

    Thanks so much! This was very helpful to determine date driven eligibility for a team!!!

    Reply
  2. James Chikanga July 22, 2021

    =DATEDIF(0,G2, "y") & IF(DATEDIF(0,G2, "y")<2, " year ", " years ") &DATEDIF(0,G2,"ym") & IF(DATEDIF(0,G2, "ym")<2, " month ", " months ") & DATEDIF(0,G2, "md") & IF(DATEDIF(0,G2, "md")<2, " day"," days")

    Reply
  3. Gina Marie July 28, 2021

    What about weeks?

    Reply

Leave a Reply