Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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.

Ages in Years and Months

by Allen Wyatt
(last updated July 3, 2018)


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 ("") 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:


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:


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 Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Ages in Years and Months.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Getting Rid of Automatic Page-Break Lines

A little trick to get rid of pagination marks when you have background pagination turned off.

Discover More

Accurately Setting Tabs Using the Ruler

If you try to set tabs by clicking on the Ruler, you may not be able to set them exactly where you want. This is normally ...

Discover More

Complex Searches for Documents

When working with lots of documents, you may have need from time to time to discover which of those documents contain ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Deciphering a Coded Date

It is no secret that Excel allows you to work with dates in your worksheets. Getting your information into a format that ...

Discover More

Tombstone Date Math

Doing math with dates is easy in Excel. Doing math with old dates, such as those you routinely encounter in genealogy, is ...

Discover More

Pushing Dates Into Last Month

Excel is great when it comes to working with dates and times. You can even do math on dates. One such easy manipulation ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 2?

2018-06-13 05:46:03


For Amy (If still pondering - unlikely) or others
Birth date Now or Other if applic Age Sort on
Thu 19 Jan 1956 Wed 13 Jun 2018 62 Yrs 4 Mths 62.33
Tue 20 Jun 1972 Wed 13 Jun 2018 45 Yrs 11 Mths 45.92
Tue 12 May 1987 Wed 13 Jun 2018 31 Yrs 1 Mths 31.08
Mon 18 Jun 2007 Wed 13 Jun 2018 10 Yrs 11 Mths 10.92
Wed 10 Mar 2010 Wed 13 Jun 2018 8 Yrs 3 Mths 8.25
Mon 21 Oct 2013 Wed 13 Jun 2018 4 Yrs 7 Mths 4.58
Sat 14 Jun 2014 Wed 13 Jun 2018 3 Yrs 11 Mths 3.92
Sat 15 Aug 2015 Sun 18 Feb 2018 2 Yrs 6 Mths 2.50

Columns A, = Birth Date
B = Now or other if applic
C = Age as per Allen mod'd ref other contributors
D = Sort on

C2 =DATEDIF(A2,B2,"y") & " Yrs " & DATEDIF(A2,B2,"ym")&" Mths"
D2 =DATEDIF(A2,B2,"y")+DATEDIF(A2,B2,"ym")/12

2017-05-11 10:39:28


Using the formula... =DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")

How do you sort the ages in chronological order and not like:

2017-02-21 11:05:31

Matt K

Need a little help if possible please.
K3 contains a text entry, in this case '12.0', B3 is their DoB and I3 is another date. The below seems to work in most cases but if, if this case, you have the trailing zero, it doesn't calculate correctly, any suggestions please?!



2017-01-28 23:42:04



=+YEAR(NOW())-YEAR(E9)-IF(OR(MONTH(NOW())<MONTH(E9),AND(MONTH(NOW())=MONTH(E9), DAY(NOW())<DAY(E9))),1,0)&" years, "&MONTH(NOW())-MONTH(E9)+IF(AND(MONTH(NOW())<=MONTH(E9),DAY(NOW())<DAY(E9)),11,IF(AND(MONTH(NOW())<MONTH(E9),DAY(NOW())>=DAY(E9)),12,IF(AND(MONTH(NOW())>MONTH(E9),DAY(NOW())<DAY(E9)),-1)))&" months, "&TEXT(NOW()-DATE(YEAR(NOW()),MONTH(NOW())-IF(DAY(NOW())<DAY(E9),1,0),DAY(E9)),0)&" days"

2016-11-05 20:26:27


Hi and thank you so much for this post.
Could you help me figure something out?
I need to determine what age a child was on a given date in the past.
I have all the DOB in column A and need to know what age they are NOW (have figured that out using your formula)
I also need to know what age they were 10 months ago (at the start of February).
While it is easy to do this manually for a few children, when it gets to hundreds I'd like to know how to do this in excel.
Thank you so much in advance....

2016-09-09 12:55:12


Perfect! Exactly what I needed. Your help is much appreciated.

2016-09-02 15:01:20


Awesome! I found this tip on the right day at the right time. Helped me out a whole lot today!

2016-07-29 06:11:00

Michael (Micky) Avidan

Check the follo'ing picture.
(see Figure 1 below)
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)

Figure 1. 

2016-07-28 05:39:57


Hi friends,
Please help me , i have two columns in excel A1 has start date and B1 has end date . now i have to calculate the how many month remaining in end date from current date.Please help if anyone have tip for this

2016-06-20 08:48:27


In know this is an Excel forum, but is there any way to do this in Word?

2016-04-05 10:47:41

If your original date (the age 60, 0 months date) is in cell B5, you can use the following formula:



2016-04-05 10:41:02


I am creating a spreadsheet for my retirement to calculate how long will my present assets last from today. (The spreadsheet has various calculations for expenses, inflation, income on assets, house value, rental value etc.)

The months are in a column with today as 0 and each next cell as +1 of the cell above.However, that column does not give me the age till my assets will last and I have to do a side calculation.

So if my age today is 60 years, 0 month, what formula do I use to calculate the next cell in the column to give me 50 years, 1 month, 50 years 2 months and so on and so forth?

2016-03-02 07:21:54


To see the number of years and Months I just modified the best formulae her (thank you) to replace "." with "Yrs" and then added the following at the end & "Mths" as shown below.

Returns XX Yrs XX Months rather than

Note that reference to E56 below is the cell where you have put the birth date.

=DATEDIF(E56,NOW(),"y") & " Yrs " & DATEDIF(E56,NOW(),"ym") &" Mths"

2016-02-15 18:08:33

Peter Atherton

Allens last formula will do it i.e.

=DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")

"y" Calculates the years between Start date and end date. Everything else is ignored

"ym" calculates the months between the dates and ignores years.

So Birth date = 29th Dec 1929 and now =
86 years + i Month

The above formula shows this as 86.1


2016-02-13 05:03:51


what is the correct formula? what is "" and "ym"? enlighten me please.

i wanna know a result such age in years and months.
thank you!

2016-01-28 01:47:41

Shaik Zawar

=DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")

what is "y" & "ym" in the formula?

2016-01-22 09:30:29

Peter Atherton

Hears the full function


2016-01-22 09:23:24

Peter Atherton`

You have to check if A1 plus 60 years is greater than the current date or not. The following will return years and can be modified.

DATEDIF(DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)),NOW(),"y"))& "yrs"

2016-01-20 03:18:17


How could I use the final formula

=DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")

to find the time difference between A1+60years and now?

2016-01-04 06:00:23


Sorry, Phil, I should have said I was using Office 2016, and your "problem" interval worked fine. I guess that's why we upgrade :).

2016-01-03 19:23:05


Well, I was using Excel 2007, so I tried it on my laptop using Excel 2010 and low and behold it worked on it. I have no idea why it won't work on Excel 2007.

2016-01-03 19:02:29


Mike: Try using a date in 2015 and then 1/1/2016. The number of days are wrong for me. It works again in mid February.I can't figure what is going wrong. Let me know if you try it. On 1/1/2016 the number of days is not right.

2016-01-03 08:22:23


Worked for me today, Phil. A little more readable if you place an initial space in the labels; e.g., " YEARS".

2016-01-03 00:09:37



2016-01-02 12:02:09


This formula worked until 01/01/2016 and it works okay saometime in February. What is going on???

2015-09-16 07:13:05


Ooops! I already figured out the answer for my simple question. Thanks

2015-09-16 07:08:11


I use the datedif function and got a 8.10 result which is correct (8 years & 10 months). But how to make it in 8Y10M format? I already tried to replace the "." to "Y" for the Year but I don't know how to appear the text M indicating the month

2015-08-21 04:44:16


this might help as well


2015-07-15 09:13:33


Robert: You can use the formulas presented in this tip, but instead of NOW(), which provides today's date, use a different cell reference, such as B1.

Then, in cell B1, put the date you mentioned (August 30, 2014).


2015-07-15 03:14:37


Oh, and to add further to my post a few seconds ago, I wanted the date format to be day/month/year rather than the standard excel date of month/day/year.

2015-07-15 03:12:44


Thanks so much for your tips Allen.

Let's say I have the birth date of a child.

I want to work out how old they are on 30th August, 2014 and likewise on 30th August, 2015.

How would I go about doing this?

2015-04-17 12:54:15


I tried the =DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym") formula - it's working great for the year but giving me nothing for the month only .0. Help!!

2015-04-15 04:47:57



The Datedif function if the best one.

2015-03-09 10:08:34


Thanks Allen! It's been a big help.

2015-02-25 12:43:32

Michael (Micky) Avidan

While cell A1 has the start date and cell A2 has the end date - try:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)

2015-02-24 09:38:25


HI how do I amend this formula so the end result is fully in months eg

24/02/2013 = 24mths.


2014-09-24 23:45:10


Thanks for sharing knowledge.

2014-07-06 10:00:06


Mkhanyisi - you asked where you find DATEDIF in Excel... it's no so much finding it... you just use it as a formula. Start with an equal sign, then type DATEDIF and Excel should display the formula format.

You also asked, if I understand correctly, about using a formula with a partial year designation. You could manipulate your formula to add 19,000,000 to it, so that 980101 becomes 19980101.

I hope this helps, but comment again if I misunderstood your questions.

2014-07-06 04:24:20

mkhanyisi xuba

Where do I find datedif in excel? I am using excel 2013. Its only date function that I find. Is possible for excel to calculate the age if the year 2000 is shorterned to 00 like in South african Identity number you only 980625 instead of 19980625, then excel picks the age.

2013-10-08 14:45:55


What I don't like about this solution is that an answer, such as "7.2" doesn't convey years and months to a reader of my worksheet. The following formula is waaaaaay long, but provides an answer such as "7 years, 3 months, 12 days"

=+YEAR(B14)-YEAR(A14)-IF(OR(MONTH(B14)<MONTH(A14),AND(MONTH(B14)=MONTH(A14), DAY(B14)<DAY(A14))),1,0)&" years, "&MONTH(B14)-MONTH(A14)+IF(AND(MONTH(B14)<=MONTH(A14),DAY(B14)<DAY(A14)),11,IF(AND(MONTH(B14)<MONTH(A14),DAY(B14)>=DAY(A14)),12,IF(AND(MONTH(B14)>MONTH(A14),DAY(B14)<DAY(A14)),-1)))&" months,"&B14-DATE(YEAR(B14),MONTH(B14)-IF(DAY(B14)<DAY(A14),1,0),DAY(A14))&" days"

2013-10-05 11:09:38

Michael (Micky) Avidan

The minus sign was not the only problem.

The pair of () are missing from the second NOW.

The formula should look like:


Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)

2013-10-05 09:30:00


Bill: Those were minus signs. I've corrected them.


2013-10-05 09:24:47

Jim Sweet

This also works and looks better.

Put =Now() in C1 and any date in C5

=IF(DATEDIF(C5,$C$1,"y")=0,"",DATEDIF(C5,$C$1,"y")&" Years ")&IF(DATEDIF(C5,$C$1,"ym")=0,"",DATEDIF(C5,$C$1,"ym")& " Months ")&DATEDIF(C5,$C$1,"md")&" days"

2013-10-05 09:01:54


What do the little squares mean in the following:
=TEXT(INT((NOW() � A1)/365.25),"##0")&"."&TEXT(INT(MOD(NOW � A1,365.25)/31),"00")

This Site

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.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.