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

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

**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!

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

Discover MoreGiven a particular week number for a year, you may want to figure out the date of the last day in that week. There is no ...

Discover MoreWant to add an ordinal suffix to a number, as in 2nd, 3rd, or 4th? Excel doesn't provide a way to do it automatically, ...

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

2018-06-13 05:46:03

Kevin

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

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

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

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

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

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(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

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

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

=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

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

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

=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

=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

2016-01-03 19:23:05

Phil

2016-01-03 19:02:29

Phil

2016-01-03 08:22:23

Michael

2016-01-03 00:09:37

dawson

THANKS PHIL.

THE FORMULA IS LENGTHIER BUT WORKS WELL.

2016-01-02 12:02:09

Phil

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

2015-08-21 04:44:16

tet_candia

this might help as well

formula:

=(DATE-DOB)/365

2015-07-15 09:13:33

awyatt

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

-Allen

2015-07-15 03:14:37

Robert

2015-07-15 03:12:44

Robert

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

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

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

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

2013-10-08 14:45:55

Marty

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

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

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

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 © 2018 Sharon Parq Associates, Inc.

## Comments