Better Excel formula converts days into Years, Months, Days

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Here’s a better Excel formula for converting a number of days into text for year, months and days.  This formula leaves out zero values and proper singular/plural (e.g. not “1 Year 0 Months 27 Days” but “1 Year 27 Days”).

And we’ll show a better way to write the formula in Excel 365, 2021/LTSC with the Let() function.

If you want to jump ahead, here’s the complete formula which shows Year/Month/Day text for a number of days. Leaving out zero values like ‘0 months’ and correct singular plural wording (e.g Year/Years.

=IF(DATEDIF(0,A1,"y")<>0,DATEDIF(0,A1,"y")&" Year"&IF(DATEDIF(0,A1,"y")>1,"s "," "),"")&IF(DATEDIF(0,A1,"ym")<>0,DATEDIF(0,A1,"ym")&" Month"&IF(DATEDIF(0,A1,"ym")>1,"s "," "),"")&IF(DATEDIF(0,A1,"md")>1,DATEDIF(0,A1,"md")&" Days","")

Where ‘A1’ is a integer number of days.

See below for an even better version using the Let() function in Excel 365/2021 and LTSC.

The usual formula

The usual formula for converting days to a text string looks like this:

=DATEDIF(0,A1,"y")&" Years "&DATEDIF(0,A1,"ym")&" Months "&DATEDIF(0,A1,"md")&" Days"

Where A1 is an integer number of days.

As you can see, it makes use of Excel’s DateDif() function to split the days into years, months and days.  DateDif usually works by calculating the numbers of days between two dates (the first two parameters). But can work with a single number of days by putting 0 (Zero) in the first parameter.

That simple approach gets most of the way but isn’t ideal.  Zero values are included and singular/plurals aren’t correct for 1 year, month or day.

Fixing those little niggles is possible with some extra IF statements.

 Let’s take it step-by-step.

Removing Zero values

Stopping the ‘0 Years’ etc means adding three IF tests to trap the zero values. For example
IF ( number <> 0, <show the number and text>,<blank>)

The whole formula is:

=IF(DATEDIF(0,A11,"y")<>0,DATEDIF(0,A11,"y")&" Years ","")&
IF(DATEDIF(0,A11,"ym")<>0,DATEDIF(0,A11,"ym")&" Months ","")&
IF(DATEDIF(0,A11,"md")<>0,DATEDIF(0,A11,"md")&" Days","")

Fixing Singular / Plural

Now add three tests for values greater than one to add an ‘s’ when necessary.

IF(< number> > 1,"s "," ")

The whole formula with the zero fix is:

=IF(DATEDIF(0,A18,"y")<>0,DATEDIF(0,A18,"y")&" Year"&IF(DATEDIF(0,A18,"y")>1,"s "," "),"")&IF(DATEDIF(0,A18,"ym")<>0,DATEDIF(0,A18,"ym")&" Month"&IF(DATEDIF(0,A18,"ym")>1,"s "," "),"")&IF(DATEDIF(0,A18,"md")>1,DATEDIF(0,A18,"md")&" Days","")

Use Let() for more readable code

That works for any version of Excel but there’s an even better option for Excel 365, Excel 2021 and Excel LTSC which have (will have) the Let() function.  Let() allows a variable to be set for use in that formula.

In this case it means settings the DaysCount once instead of changing the eight appearances. Let() can also speed up calculations but that benefit is very tiny for this simple formula.

Here’s the LET() version of the above formula.  The third parameter is the same the above formula except the days value is now a variable called DaysCount. 

DaysCount is named and defined by the first two parameters of Let()

IF(DATEDIF(0,DaysCount,"y")<>0,DATEDIF(0,DaysCount,"y")&" Year"&IF(DATEDIF(0,DaysCount,"y")>1,"s "," "),"")&
IF(DATEDIF(0,DaysCount,"ym")<>0,DATEDIF(0,DaysCount,"ym")&" Month"&
IF(DATEDIF(0,DaysCount,"ym")>1,"s "," "),"")&IF(DATEDIF(0,DaysCount,"md")>1,DATEDIF(0,DaysCount,"md")&" Days",""))

How old in years, months and days

In that simple example, the days value is already in the cell (A25) but it’s easily changed to pass in a calculation of the days.  For example, this calculation of exact ages.

The number of days is calculated once by Let() as Today() minus the birthday as an Excel serial date.

=LET(DaysCount, TODAY()-Birthday,
IF(DATEDIF(0,DaysCount,"y")<>0,DATEDIF(0,DaysCount,"y")&" Year"&IF(DATEDIF(0,DaysCount,"y")>1,"s "," "),"")&IF(DATEDIF(0,DaysCount,"ym")<>0,DATEDIF(0,DaysCount,"ym")&" Month"&IF(DATEDIF(0,DaysCount,"ym")>1,"s "," "),"")&IF(DATEDIF(0,DaysCount,"md")>1,DATEDIF(0,DaysCount,"md")&" Days",""))

LET() assigns names to calculations in Excel
Text to Excel Date conversion by adding Zero with Paste Special
Complete Excel NetworkDays() solution with holidays & vacations
Excel’s NetworkDays() and Holidays in the real world

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address