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

43

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, and 2013. 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

Making Word Remember My Settings

Ever had the experience of setting some configuration option in Word, only to have the option revert to a different setting ...

Discover More

Jumping to a Relative Line Number

As you navigate through a document, you may have a need to move forward or backward a specific number of lines. This is easy ...

Discover More

Controlling Display of the Formula Bar

The formula bar is where you can see exactly what is stored in a spreadsheet cell. If you would rather hide the formula bar, ...

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)

Counting Dates in a Range

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

Discover More

Finding the Dates for Minimums and Maximums

If you use Excel to maintain a collection of data, you may need to find information in one column based on information in an ...

Discover More

Displaying a Number as Years and Months

How do you display a number of years, such as 3.67 years, as a number of years and months? It's simple to do with a formula, ...

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.

Comments

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 6 + 0?

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.

Newest Tips
Subscribe

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.