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

Turning the Legend On and Off

When you create a chart in Excel, the program may automatically add a legend that explains the contents of the chart. In some ...

Discover More

Counting the Number of Blank Cells

If you need to count the number of blank cells in a range, the function to use is COUNTBLANK. This tip discusses the ...

Discover More

Forcing Printouts to Black and White

If you want to force Word to print some of its colors in black and white, you may be out of luck. One bright spot, as ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

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

Discover More

Copying Dates a Year Into the Future

Need to copy a range of dates and update them to a different year? Here are a number of ways to accomplish this task with a ...

Discover More

Inserting Tomorrow's Date

You can use a couple of different worksheet functions to enter today's date in a cell. What if you want to calculate ...

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. 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 9 + 4?

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.