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.
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 Data Analysis and Business Modeling today!
Excel makes working with a list of dates relatively easy. If you have a list of dates, you may need to know how many of ...
Discover MoreWhen entering times in a worksheet, you may have a need to round whatever you enter to the nearest 15-minute increment. ...
Discover MoreDates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-03-12 14:47:09
J. Woolley
Re. Function YrMoDa2 in my most recent comment below, it has come to my attention that the result for Mo can become negative; therefore, the following statementn If Mo < 0 Then Yr = Yr - 1: Mo = 12 + Monshould be inserted immediately before this statementn YrMoDa2 = Yr & " yr " & Mo & " mth " & Da & " day"nThe same correction was made to the latest version of TimeDif in My Excel Toolbox.nSee https://sites.google.com/view/MyExcelToolbox/
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:nnFunction YrMoDa2(Start As Date, Finish As Date) As Stringn Dim nS As Long, nF As Long, nX As Longn Dim Yr As Long, Mo As Long, Da As Longn nS = Int(Start)n nF = Int(Finish)n Da = Day(nF) - Day(nS)n If Da < 0 Then Da = Da + Day(DateSerial(Year(nS), Month(nS) + 1, 0))n Yr = DateDiff("yyyy", nS, nF)n If DateAdd("yyyy", Yr, nS) > nF Then Yr = Yr - 1n nX = DateAdd("yyyy", Yr, nS)n Mo = DateDiff("m", nX, nF)n If DateAdd("m", Mo, nX) > nF - Da Then Mo = Mo - 1n YrMoDa2 = Yr & " yr " & Mo & " mth " & Da & " day"nEnd FunctionnnTo work with dates earlier than Excel's limit of January 1, 1900 (or 1904), use text values with 4-digit years. For example:n =YrMoDa2("July 4, 1776", TODAY())nThe 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.nThe YrMoDa2 method has been implemented in the latest version of TimeDif.nSee https://sites.google.com/view/MyExcelToolbox/nnBy the way, my earliest comment below described these formulas for the Tip's requirement, assuming less than 100 years between Start and Finish:n =LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) & "." & MID(x, 8, 2)) -- textn =LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) + MID(x, 8, 2)/100) -- numericnMy Excel Toolbox includes the SplitText function described in my comment here: https://excelribbon.tips.net/T013906#comment-form-hdnUsing SplitText, these formulas yield the same text and numeric results without any assumption:n =LET(x, SplitText(TRIM(TimeDif(A1, TODAY()))), n INDEX(x, 1) & "." & INDEX(x, 3))n =LET(x, SplitText(TRIM(TimeDif(A1, TODAY()))), n INDEX(x, 1) + INDEX(x, 3)/100)
2025-03-09 16:13:05
J. Woolley
@fred potternLove your calendar-based formula. Here's a simplified version:nn=DATEDIF(A3, B3, "Y") & " yr " & DATEDIF(A3, B3, "YM") & " mth " & IF(DAY(B3) < DAY(A3), DAY(EOMONTH(A3, 0))) + DAY(B3) - DAY(A3) & " day"nnAnd here's the equivalent UDF:nnFunction YrMoDa(Start As Date, Finish As Date) As Stringn Dim nS As Long, nF As Long, Yr As Long, Mo As Long, Da As Longn nS = Int(Start)n nF = Int(Finish)n Yr = Evaluate("DATEDIF(" & nS & "," & nF & ",""Y"")")n Mo = Evaluate("DATEDIF(" & nS & "," & nF & ",""YM"")")n Da = Day(nF) - Day(nS)n If Da < 0 Then Da = Da + Day(WorksheetFunction.EoMonth(nS, 0))n YrMoDa = Yr & " yr " & Mo & " mth " & Da & " day"nEnd FunctionnnNotice 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.nn=LET(startDate, $A$3,n endDate, $B$3,nn years, DATEDIF(startDate, endDate, "Y"),n Months, DATEDIF(startDate, endDate, "YM"),n StartDay, DAY(startDate),n EndDay, DAY(endDate),n monthEnd, DAY(EOMONTH(startDate, 0)),nn Days,IF(StartDay = EndDay, "",n IF(StartDay <= EndDay,n EndDay - StartDay,n monthEnd - StartDay + EndDay )),nn YearsText, IF(years = 0, "", years & "yr" & IF(years > 1, "s ", " ")),n MonthsText, IF(Months = 0, "", Months & "mth" & IF(Months > 1, "s ", " ")),n DaysText, IF(Days = "", "", Days & "day" & IF(Days > 1, "s", "")),n YearsText & MonthsText & DaysText)
2025-03-07 04:44:49
Mike J
@frednnBrilliant - and, surprisingly, shorter too!
2025-03-06 14:59:28
fred potter
@MikeJnIt's amazing what you can do at 3:30 in the morning.nnA3 has StartDate and B3 has EndDate.nn=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 JnThank 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.nBesides 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:nhttps://stackoverflow.com/a/11179293/10172433nhttps://stackoverflow.com/a/35803502/10172433nhttp://www.vbaexpress.com/kb/getarticle.php?kb_id=866nhttps://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38nhttps://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
@frednnSorry, 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?nnn29/01/1961 05/03/2025 64yrs 1mth 6daysn30/01/1961 05/03/2025 64yrs 1mth 5daysn31/01/1961 05/03/2025 64yrs 1mth 4daysn01/02/1961 05/03/2025 64yrs 1mth 31daysn02/02/1961 05/03/2025 64yrs 1mth 30daysn03/02/1961 05/03/2025 64yrs 1mth 29daysn04/02/1961 05/03/2025 64yrs 1mth 28daysn05/02/1961 05/03/2025 64yrs 1mth n06/02/1961 05/03/2025 64yrs 26daysn07/02/1961 05/03/2025 64yrs 25daysn08/02/1961 05/03/2025 64yrs 24days
2025-03-04 21:17:14
fred potter
@MikeJnnI think I may have cracked it:n'=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",""))nnMaybe there's a more elegant way, but this works against all of my important dates.
2025-03-03 04:23:17
Mike J
@frednI 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.nI 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
@MikeJnnThanks for pointing out the DATEDIF error, I may have been sending out wrong information for the past 40+ years .nI took a dive into my old-old workbooks and found a VBA option that I may fiddle with and use from now on:nn'Found on superuser.com - Gary's Student 2017nPublic Function ddif(d1 As Date, d2 As Date) As Stringn Dim years As Long, months As Long, days As Longnn years = 0n months = 0n days = 0n Year1 = Year(d1)n month1 = Month(d1)n Day1 = Day(d1)nn Don If DateSerial(Year1 + years, month1, Day1) = d2 Thenn GoTo finishedn End Ifn If DateSerial(Year1 + years, month1, Day1) > d2 Thenn years = years - 1n Exit Don End Ifn years = years + 1n Loopn Don If DateSerial(Year1 + years, month1 + months, Day1) = d2 Thenn GoTo finishedn End Ifn If DateSerial(Year1 + years, month1 + months, Day1) > d2 Thenn months = months - 1n Exit Don End Ifn months = months + 1n Loopn Don If DateSerial(Year1 + years, month1 + months, Day1 + days) = d2 Thenn GoTo finishedn End Ifn days = days + 1n Loopnfinished:n ddif = years & " yrs " & months & " mths " & days & " days"nEnd Function
2025-03-01 17:40:11
Mike J
@J.WoolleynnI think your Timedif() UDF is suffering from similar issues. e.g. using "=TimeDif(A1, TODAY())",nn31/01/1961 produces 64 yr 01 mth -02 day 00 hr 00 min 00 sec when today = "01/03/2025"nnI 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 @PFLnnRe my earlier comment, apparently, this is a MS "known issue" when using the "MD" argument with DATEDIF().nnFor workaround to get correct days, see:-nnhttps://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.nnhttps://techcommunity.microsoft.com/discussions/excelgeneral/datedif-function/3733820nnSee the comment by Riny near the bottom.nnPFL
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().nMy Excel Toolbox includes the following function:n =TimeDif(Start, Finish, [Approximate], [Conversational])nStart 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.nFor example, today is March 1, 2025, so if A1 is DATE(2015, 3, 17) or the text equivalent value "March 17, 2015" then this formulan =TimeDif(A1, TODAY(), TRUE)nreturns the following approximate textn almost 10 yearsnand this formulan =TimeDif(A1, TODAY(), , TRUE)nreturns the following conversational textn 9 years 11 months and 12 daysnand this formulan =TimeDif(A1, TODAY())nreturns 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 minn 09 yr 11 mth 12 day 00 hr 00 min 00 secnTherefore, this formulan =LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) & "." & MID(x, 8, 2))nreturns 09.11 as text, and this formulan =LET(x, TimeDif(A1, TODAY()), LEFT(x, 2) + MID(x, 8, 2)/100)nreturns 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.nSee https://sites.google.com/view/MyExcelToolbox/
2025-03-01 12:48:56
Mike J
@frednI think DATEDIF() has problems in your formulanConsider the following dates and you formula results:nnDate of Birth =Today() '01/03/2025n26/01/1961 64yrs 1mth 3days n27/01/1961 64yrs 1mth 2days n28/01/1961 64yrs 1mth 1day n29/01/1961 64yrs 1mth n30/01/1961 64yrs 1mth -1day n31/01/1961 64yrs 1mth -2day n01/02/1961 64yrs 1mth n02/02/1961 64yrs 27days n03/02/1961 64yrs 26days n04/02/1961 64yrs 25days nnI 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. nnApplying Allen's formula over a sample year of dates, I see results like: n66.1n66.0n65.11n65.10n65.9n...n65.2n65.1n...nnNotice the values 65.1 and 65.10 nIf any downstream math is done with these numbers, they will both be evaluated as 65.1nnI propose two, more-robust alternatives: n= DATEDIF( A1, NOW(), "y" ) & "." & TEXT( DATEDIF( A1, NOW(), "ym"), "00" )n= DATEDIF( A1, NOW(), "y" ) + DATEDIF( A1, NOW(), "ym") / 100nnThe 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:nn=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 © 2026 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