Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Ages in Years and Months.
Written by Allen Wyatt (last updated March 1, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Many times, in early childhood education, a child's age in Years.Months format is needed for reporting, admitting, and evaluation. Basically, this format shows the number of elapsed years and months since birth, separated by a period.
There are several ways you can go about getting the desired age. The formula you choose to do the work depends, quite literally, on your preference in formulas.
Assuming that you have the birth date in cell A1, you might think you could use some math on the date to determine the proper information. For instance, you might think that you could use this formula:
=NOW() - A1
This produces a value that is the difference between the two dates, but if you then apply a custom format ("yy.mm") to the result, you'll find out that it doesn't give the sought-after Years.Months. You get something close; the years are right, but the months vary from 1 to 12 when they should vary from 0 to 11. (Somebody can be 12 years and 0 months old, but it is not proper to say 12 years and 12 months old.)
This means that you need to start casting about for a formulaic approach. A simple formula would be the following:
=YEAR(NOW())-YEAR(A1) & "." & MONTH(NOW())-MONTH(A1)
There are many variations on the above, but the problem is that they all only look at the month of today compared to the month of the birthdate; they don't take the day of the month into account. What is that important? Because the number of elapsed months since birth is dependent on how the day of the birth month compares to the day of the present month, and the formula doesn't allow for that distinction.
So, you might consider a more complex formula that actually tries to calculate elapsed years and months, such as the following one:
=TEXT(INT((NOW()-A1)/365.25),"##0")&"."&TEXT(INT(MOD(NOW()-A1,365.25)/31),"00")
This comes much closer to what is needed, but it is still possible to be off by a day or two right around the time of the month when the birth day is approached. Closer still is a formula which uses the YEARFRAC function:
=ROUNDDOWN(YEARFRAC(A1,TODAY()),0)&"."&ROUNDDOWN((YEARFRAC(A1,TODAY())-ROUNDDOWN(YEARFRAC(A1,TODAY()),0))*12,0)
The best (and simplest) formulaic approach, however, is this one:
=DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")
The formula relies on the DATEDIF function, which determines the difference between two dates. The value returned by the function depends on the third parameter passed to the function. In this case, the first invocation of DATEDIF returns the number of elapsed years and the second returns the number of elapsed months. It provides the most accurate results of any of the formulas discussed so far.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9148) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Ages in Years and Months.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering ...
Discover MoreYou can use Excel to work with times and dates. Sometimes, however, figuring out the best way to do that can be tricky. ...
Discover MoreNeed to calculate the date that is a certain number of workdays in the future? You can do so using a couple of different ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-03-10 15:56:18
J. Woolley
Re. my most recent comment below, I've finally determined a reliable method for wider ranging VBA dates as well as Excel dates:
Function YrMoDa2(Start As Date, Finish As Date) As String
Dim nS As Long, nF As Long, nX As Long
Dim Yr As Long, Mo As Long, Da As Long
nS = Int(Start)
nF = Int(Finish)
Da = Day(nF) - Day(nS)
If Da < 0 Then Da = Da + Day(DateSerial(Year(nS), Month(nS) + 1, 0))
Yr = DateDiff("yyyy", nS, nF)
If DateAdd("yyyy", Yr, nS) > nF Then Yr = Yr - 1
nX = DateAdd("yyyy", Yr, nS)
Mo = DateDiff("m", nX, nF)
If DateAdd("m", Mo, nX) > nF - Da Then Mo = Mo - 1
YrMoDa2 = Yr & " yr " & Mo & " mth " & Da & " day"
End Function
To work with dates earlier than Excel's limit of January 1, 1900 (or 1904), use text values with 4-digit years. For example:
=YrMoDa2("July 4, 1776", TODAY())
The result for today (March 10, 2025) is 248 yr 8 mth 6 day. Alternate date formats like "1776-Jul-4" or "1776, 4 Jul" consistent with your regional calendar setting are also supported.
The YrMoDa2 method has been implemented in the latest version of TimeDif.
See https://sites.google.com/view/MyExcelToolbox/
By the way, my earliest comment below described these formulas for the Tip's requirement, assuming less than 100 years between Start and Finish:
=LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) & "." & MID(x, 8, 2)) -- text
=LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) + MID(x, 8, 2)/100) -- numeric
My Excel Toolbox includes the SplitText function described in my comment here: https://excelribbon.tips.net/T013906#comment-form-hd
Using SplitText, these formulas yield the same text and numeric results without any assumption:
=LET(x, SplitText(TRIM(TimeDif(A1, TODAY()))),
INDEX(x, 1) & "." & INDEX(x, 3))
=LET(x, SplitText(TRIM(TimeDif(A1, TODAY()))),
INDEX(x, 1) + INDEX(x, 3)/100)
2025-03-09 16:13:05
J. Woolley
@fred potter
Love your calendar-based formula. Here's a simplified version:
=DATEDIF(A3, B3, "Y") & " yr " & DATEDIF(A3, B3, "YM") & " mth " & IF(DAY(B3) < DAY(A3), DAY(EOMONTH(A3, 0))) + DAY(B3) - DAY(A3) & " day"
And here's the equivalent UDF:
Function YrMoDa(Start As Date, Finish As Date) As String
Dim nS As Long, nF As Long, Yr As Long, Mo As Long, Da As Long
nS = Int(Start)
nF = Int(Finish)
Yr = Evaluate("DATEDIF(" & nS & "," & nF & ",""Y"")")
Mo = Evaluate("DATEDIF(" & nS & "," & nF & ",""YM"")")
Da = Day(nF) - Day(nS)
If Da < 0 Then Da = Da + Day(WorksheetFunction.EoMonth(nS, 0))
YrMoDa = Yr & " yr " & Mo & " mth " & Da & " day"
End Function
Notice DATEDIF is not available as a method of the WorksheetFunction object. Start and Finish can be text or numeric, but they must be Excel dates, which start at January 1, 1900 (or 1904). On the other hand, VBA dates start at January 1, 0100. The TimeDif function in My Excel Toolbox was intended to work with wider ranging VBA dates (like "July 4, 1776"), but so far I haven't determined a reliable method. In the meantime, I've implemented the YrMoDa method with attribution to Potter's latest comment below. This version returns #VALUE! if Start and Finish are not Excel dates.
2025-03-08 16:15:31
Jeff C
@fred potter that's amazing! So glad you shared that. I converted it to a LET and it's working great. Gonna replace A3 and B3 with named cells next and make a lambda with it. Thanks again this is going to come in super handy.
=LET(startDate, $A$3,
endDate, $B$3,
years, DATEDIF(startDate, endDate, "Y"),
Months, DATEDIF(startDate, endDate, "YM"),
StartDay, DAY(startDate),
EndDay, DAY(endDate),
monthEnd, DAY(EOMONTH(startDate, 0)),
Days,IF(StartDay = EndDay, "",
IF(StartDay <= EndDay,
EndDay - StartDay,
monthEnd - StartDay + EndDay )),
YearsText, IF(years = 0, "", years & "yr" & IF(years > 1, "s ", " ")),
MonthsText, IF(Months = 0, "", Months & "mth" & IF(Months > 1, "s ", " ")),
DaysText, IF(Days = "", "", Days & "day" & IF(Days > 1, "s", "")),
YearsText & MonthsText & DaysText)
2025-03-07 04:44:49
Mike J
@fred
Brilliant - and, surprisingly, shorter too!
2025-03-06 14:59:28
fred potter
@MikeJ
It's amazing what you can do at 3:30 in the morning.
A3 has StartDate and B3 has EndDate.
=IF(DATEDIF(A3,B3,"Y")=0,"",DATEDIF(A3,B3,"Y")&"yr"&IF(DATEDIF(A3,B3,"Y")>1,"s "," "))&IF(DATEDIF(A3,B3,"YM")=0,"", "" &DATEDIF(A3,B3,"YM") & "mth" &IF(DATEDIF(A3,B3,"YM")>1,"s "," "))&IF(DAY(A3)=DAY(B3),"",IF(DAY(A3)<=DAY(B3),DAY(B3)-DAY(A3)&"day"&IF(DAY(B3)-DAY(A3)>1,"s",""),DAY(EOMONTH(A3,0)) - DAY(A3)+DAY(B3)&"day"&IF(DAY(EOMONTH(A3,0)) - DAY(A3)+DAY(B3)>1,"s","")))
2025-03-05 15:02:33
J. Woolley
@Mike J
Thank you for identifying the error in TimeDif (which does not use DATEDIF). This is a more difficult problem than I expected, but I will keep trying to fix it. In the meantime, I disabled TimeDif in the latest version of My Excel Toolbox.
Besides the unreliable methods you identified (Gary's Student at https://superuser.com/a/1222668/771860 and Chip Pearson's at http://www.cpearson.com/excel/datedif.aspx#Age) here are some more that should not be used:
https://stackoverflow.com/a/11179293/10172433
https://stackoverflow.com/a/35803502/10172433
http://www.vbaexpress.com/kb/getarticle.php?kb_id=866
https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
https://www.access-programmers.co.uk/forums/threads/from-number-of-days-to-years-months-and-days-datediff-madness.315684/post-1741749
2025-03-05 04:30:33
Mike J
@fred
Sorry, but it doesn't seem to work with my original test dates. Mind you, I've had no luck either! Where are the Gurus when you need them?
29/01/1961 05/03/2025 64yrs 1mth 6days
30/01/1961 05/03/2025 64yrs 1mth 5days
31/01/1961 05/03/2025 64yrs 1mth 4days
01/02/1961 05/03/2025 64yrs 1mth 31days
02/02/1961 05/03/2025 64yrs 1mth 30days
03/02/1961 05/03/2025 64yrs 1mth 29days
04/02/1961 05/03/2025 64yrs 1mth 28days
05/02/1961 05/03/2025 64yrs 1mth
06/02/1961 05/03/2025 64yrs 26days
07/02/1961 05/03/2025 64yrs 25days
08/02/1961 05/03/2025 64yrs 24days
2025-03-04 21:17:14
fred potter
@MikeJ
I think I may have cracked it:
'=IF(DATEDIF(A3,B3,"Y")=0,"",DATEDIF(A3,B3,"Y")&"yr"&IF(DATEDIF(A3,B3,"Y")>1,"s "," "))&IF(DATEDIF(A3,B3,"YM")=0,"", "" &DATEDIF(A3,B3,"YM") & "mth" &IF(DATEDIF(A3,B3,"YM")>1,"s "," ")) &IF(DAY(A3)=DAY(B3),"",IF(B3-DATE(YEAR(B3),MONTH(B3),1)=0,1+DAY(EOMONTH(A3,0))-DAY(A3),B3-DATE(YEAR(B3),MONTH(B3),1)+DAY(EOMONTH(A3,0))-DAY(A3))& "day"&IF(IF(B3-DATE(YEAR(B3),MONTH(B3),1)=0,1+DAY(EOMONTH(A3,0))-DAY(A3),B3-DATE(YEAR(B3),MONTH(B3),1)+DAY(EOMONTH(A3,0))-DAY(A3))>1,"s",""))
Maybe there's a more elegant way, but this works against all of my important dates.
2025-03-03 04:23:17
Mike J
@fred
I think you may still have some tinkering to do. Gary's Student 2017's UDF doesn't work either, and neither does Chip Pearsons formula from the same site.
I wonder how many children have been incorrectly assigned over the years - although, to be fair, most of the solutions are accurate up to the month - it's the days that are a problem.
2025-03-02 18:32:23
fred potter
@MikeJ
Thanks for pointing out the DATEDIF error, I may have been sending out wrong information for the past 40+ years .
I took a dive into my old-old workbooks and found a VBA option that I may fiddle with and use from now on:
'Found on superuser.com - Gary's Student 2017
Public Function ddif(d1 As Date, d2 As Date) As String
Dim years As Long, months As Long, days As Long
years = 0
months = 0
days = 0
Year1 = Year(d1)
month1 = Month(d1)
Day1 = Day(d1)
Do
If DateSerial(Year1 + years, month1, Day1) = d2 Then
GoTo finished
End If
If DateSerial(Year1 + years, month1, Day1) > d2 Then
years = years - 1
Exit Do
End If
years = years + 1
Loop
Do
If DateSerial(Year1 + years, month1 + months, Day1) = d2 Then
GoTo finished
End If
If DateSerial(Year1 + years, month1 + months, Day1) > d2 Then
months = months - 1
Exit Do
End If
months = months + 1
Loop
Do
If DateSerial(Year1 + years, month1 + months, Day1 + days) = d2 Then
GoTo finished
End If
days = days + 1
Loop
finished:
ddif = years & " yrs " & months & " mths " & days & " days"
End Function
2025-03-01 17:40:11
Mike J
@J.Woolley
I think your Timedif() UDF is suffering from similar issues. e.g. using "=TimeDif(A1, TODAY())",
31/01/1961 produces 64 yr 01 mth -02 day 00 hr 00 min 00 sec when today = "01/03/2025"
I don't think your macro uses the DATEDIF() function, but all these issues may explain why DATEDIF() does not appear in Intellisense or the Function help file (at least not in excel 2010). Is it there for LOTUS123 bug compatibility, like 1904?
2025-03-01 16:49:37
Mike J
@fred @PFL
Re my earlier comment, apparently, this is a MS "known issue" when using the "MD" argument with DATEDIF().
For workaround to get correct days, see:-
https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
2025-03-01 15:28:04
PFL
For a discussion of some problems with DATEDIF, this URL to a help forum sheds some light on the difficulty.
https://techcommunity.microsoft.com/discussions/excelgeneral/datedif-function/3733820
See the comment by Riny near the bottom.
PFL
2025-03-01 15:25:09
J. Woolley
Many of the formulas presented here use the NOW() function, which returns the current date and time. I believe TODAY() is more appropriate because the current time is ignored when determining age in this Tip. Notice INT(NOW()) yields the same numeric result as TODAY().
My Excel Toolbox includes the following function:
=TimeDif(Start, Finish, [Approximate], [Conversational])
Start and Finish must be numeric or text dates and/or times. TimeDif returns the difference between Start and Finish as text, which can optionally be exact, approximate, or conversational. The optional arguments are FALSE by default, yielding an exact result.
For example, today is March 1, 2025, so if A1 is DATE(2015, 3, 17) or the text equivalent value "March 17, 2015" then this formula
=TimeDif(A1, TODAY(), TRUE)
returns the following approximate text
almost 10 years
and this formula
=TimeDif(A1, TODAY(), , TRUE)
returns the following conversational text
9 years 11 months and 12 days
and this formula
=TimeDif(A1, TODAY())
returns the following exact text with two digit values followed by one space and a label plus two spaces after each label yr, mth, day, hr, and min
09 yr 11 mth 12 day 00 hr 00 min 00 sec
Therefore, this formula
=LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) & "." & MID(x, 8, 2))
returns 09.11 as text, and this formula
=LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) + MID(x, 8, 2)/100)
returns the number 9.11, which should be formatted as Numeric with 2 decimal places. Notice both formulas assume less than 100 years between Start and Finish.
See https://sites.google.com/view/MyExcelToolbox/
2025-03-01 12:48:56
Mike J
@fred
I think DATEDIF() has problems in your formula
Consider the following dates and you formula results:
Date of Birth =Today() '01/03/2025
26/01/1961 64yrs 1mth 3days
27/01/1961 64yrs 1mth 2days
28/01/1961 64yrs 1mth 1day
29/01/1961 64yrs 1mth
30/01/1961 64yrs 1mth -1day
31/01/1961 64yrs 1mth -2day
01/02/1961 64yrs 1mth
02/02/1961 64yrs 27days
03/02/1961 64yrs 26days
04/02/1961 64yrs 25days
I also tried adding &"."&DATEDIF(A1,NOW(),"md") onto the end of Allen's final formula, and similar errors occurred.
2025-03-01 10:34:06
Dave Bonin
While Allen's last formula works, its results can be corrupted depending on what further processing is done with the results.
Applying Allen's formula over a sample year of dates, I see results like:
66.1
66.0
65.11
65.10
65.9
...
65.2
65.1
...
Notice the values 65.1 and 65.10
If any downstream math is done with these numbers, they will both be evaluated as 65.1
I propose two, more-robust alternatives:
= DATEDIF( A1, NOW(), "y" ) & "." & TEXT( DATEDIF( A1, NOW(), "ym"), "00" )
= DATEDIF( A1, NOW(), "y" ) + DATEDIF( A1, NOW(), "ym") / 100
The first produces a text result, the second a numeric result.
2025-03-01 05:05:44
fred potter
And for Years, Months and Days you could use something like this:
=TRIM(IF(DATEDIF(A1,TODAY(),"Y")=0,"",DATEDIF(A1,TODAY(),"Y")& "yr" & IF(DATEDIF(A1,TODAY(),"Y")>1,"s","")) &IF(DATEDIF(A1,TODAY(),"YM")=0,"", " " &DATEDIF(A1,TODAY(),"YM") & "mth" &IF(DATEDIF(A1,TODAY(),"YM")>1,"s","")) &IF(DATEDIF(A1,TODAY(),"MD")=0,"", " " &DATEDIF(A1,TODAY(),"MD") & "day" &IF(DATEDIF(A1,TODAY(),"MD")>1,"s","")))
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments