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 an even 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 Calculate age and other interesting facts using Excel and DateDif() for a primer on DateDif().
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()
=LET(DaysCount,A25, 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",""))
Calculate age and other interesting facts using Excel and DateDif()
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
