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

##### MORE FROM ALLEN

Inserting Rows

Need to insert rows in your worksheet? Excel provides a few techniques you can use to do this. Here are some ideas you ...

Discover More

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...

Discover More

Want to modify the way your text flows between pages in a document? Word allows you to insert several types of breaks ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

##### More ExcelTips (ribbon)

ISO Week Numbers in Excel

Work in an industry that uses ISO standards when it comes to working with dates? You'll love the formula in this tip ...

Discover More

Calculating the First Business Day of the Month

Want to know which day of the month is the first business day? The easiest way to determine the date is to use the ...

Discover More

Using Early Dates

Excel is brilliant at handling dates—as long as they aren't dates earlier than the base date used by the program. ...

Discover More
##### Subscribe

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 six more than 7?

2018-06-13 05:46:03

Kevin

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

Formula
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

Amy

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

How do you sort the ages in chronological order and not like:
86
83
8
72
7
66
65
64
6
54

2017-02-21 11:05:31

Matt K

Hi,
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?!

=K3-(DATEDIF(B3,I3,"y")&"."&DATEDIF(B3,I3,"ym"))

Thanks.
Matt

2017-01-28 23:42:04

KIRIT JAIN

@MARTY WITH LITTLE CHANGE IN YOUR FORMULA

=+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

Angela

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)
AND
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....
Angie

2016-09-09 12:55:12

Kathy

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

2016-09-02 15:01:20

Cheryll

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

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

Figure 1.

2016-07-28 05:39:57

VARUN

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

Vincent

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

2016-04-05 10:47:41

allen@sharonparq.com

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

=IF(MONTH(B5)=12,DATE(YEAR(B5)+1,1,DAY(B5)),DATE(YEAR(B5),MONTH(B5)+1,DAY(B5)))

-Allen

2016-04-05 10:41:02

Charles

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

Henk

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
XX.XX.

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

HTH

2016-02-13 05:03:51

eathan

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

=IF(DATE(YEAR(A1)+60,MONTH(A1),DAY(A1))>NOW(),
DATEDIF(NOW(),C16,"y")&"."&DATEDIF(NOW(),C16,"ym"),
DATEDIF(DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)),NOW(),"y"))&"."&DATEDIF(DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)),NOW(),"ym")

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.

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

2016-01-20 03:18:17

User03

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

Michael

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

Phil

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

Phil

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

Michael

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

dawson

THANKS PHIL.
THE FORMULA IS LENGTHIER BUT WORKS WELL.

2016-01-02 12:02:09

Phil

=DATEDIF(A1,TODAY(),"Y")&"YEARS,"&DATEDIF(A1,TODAY(),"YM")&"MONTHS,"&DATEDIF(A1,TODAY(),"MD")&"DAYS"
This formula worked until 01/01/2016 and it works okay saometime in February. What is going on???

2015-09-16 07:13:05

Jhun

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

2015-09-16 07:08:11

Jhun

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

tet_candia

this might help as well

formula:
=(DATE-DOB)/365

2015-07-15 09:13:33

awyatt

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).

-Allen

2015-07-15 03:14:37

Robert

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

Robert

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

Crystal

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

Alok

Thanx

The Datedif function if the best one.

2015-03-09 10:08:34

Lovely

Thanks Allen! It's been a big help.

2015-02-25 12:43:32

Michael (Micky) Avidan

@KIMBERLEYץ
While cell A1 has the start date and cell A2 has the end date - try:
=DATEDIF(start_date,end_date,"m")
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

2015-02-24 09:38:25

KIMBERLEY

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

Yaqoob

Thanks for sharing knowledge.

2014-07-06 10:00:06

Marty

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

Marty

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:

=TEXT(INT((NOW()-A1)/365.25),"##0")&"."&TEXT(INT(MOD(NOW()-A1,365.25)/31),"00")

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

2013-10-05 09:30:00

awyatt

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

-Allen

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

Bill

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.