how can I calculate chronological age in excel
 

I am trying to calculate chronological age - years and complete months - for
school report purposes. I can get Years and months but not accurately - say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.

Answer: how can I calculate chronological age in excel
 

To calculate chronological age in Excel, you can use the DATEDIF function. Here's how:

1. In a new cell, enter the formula "=DATEDIF(A2,TODAY(),"y")", where A2 is the cell containing the date of birth and "y" stands for years. This will calculate the number of years between the date of birth and today's date.
   
2. In another cell, enter the formula "=DATEDIF(A2,TODAY(),"ym")", where A2 is the cell containing the date of birth and "ym" stands for months. This will calculate the number of months between the date of birth and today's date.
   
3. To combine the two results, you can use the "&" symbol to join them together. For example, if the first formula is in cell B2 and the second formula is in cell C2, you can enter the formula "=B2&" years, "&C2&" months"" in a new cell. This will display the result as "9 years, 0 months" for the example you provided.
   

Note that the DATEDIF function is a bit tricky to use, as it doesn't appear in the function list and requires you to enter the arguments manually. The syntax is "=DATEDIF(start_date,end_date,unit)", where start_date and end_date are the two dates you want to calculate the difference between, and unit is the unit of time you want to use (e.g. "y" for years, "m" for months, "d" for days).

Hi Mike

here's a formula from Chip Pearson's web site
(http://www.cpearson.com/excel/datedif.htm#Age)

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"

to use it with a given date (15 Dec 04) rather than the current date (Now())
use the following formula

=DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " &
DATEDIF(A1,B1,"md") & " days"

where A1 stores their birthdate and B1 stores the date to compare to

Cheers
JulieD


"Mike New" <Mike wrote in message
...
I am trying to calculate chronological age - years and complete months -
for
school report purposes. I can get Years and months but not accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.

see Chip Pearson's page:
DATEDIF Function
http://www.cpearson.com/excel/datedif.htm

Basically does calculations same as most people would do them.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mike New" <Mike wrote in message ...
I am trying to calculate chronological age - years and complete months - for
school report purposes. I can get Years and months but not accurately - say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.

Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years and 1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete months - for
school report purposes. I can get Years and months but not accurately - say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.

Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years and
1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete months -
for
school report purposes. I can get Years and months but not accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.

It would actually quicker to keep the formula in the worksheet. And if you keep
the formula handy (in a text file or sample workbook or just a link to Chip's
site), it wouldn't be too hard to copy it into your worksheet.

But if you want...

Option Explicit
Function YrsMthsDays(rng1 As Range, rng2 As Range) As Variant

Dim iCtr As Long
Dim myVals(0 To 2) As Long
Dim myIntervals As Variant
Dim myStrs As Variant
Dim myOutput As String

myIntervals = Array("y", "ym", "md")
myStrs = Array(" years, ", " months, ", " days")

myOutput = ""
For iCtr = LBound(myVals) To UBound(myVals)
myVals(iCtr) = Application.Evaluate("datedif(" _
& rng1.Address(external:=True) _
& "," & rng2.Address(external:=True) _
& ",""" & myIntervals(iCtr) & """)")
myOutput = myOutput & myVals(iCtr) & myStrs(iCtr)
Next iCtr

YrsMthsDays = myOutput

End Function

Rob wrote:

Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years and
1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete months -
for
school report purposes. I can get Years and months but not accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.


--

Dave Peterson

Thanks Dave, just what I needed, helps with my learning cycle! Rob

"Dave Peterson" wrote in message
...
It would actually quicker to keep the formula in the worksheet. And if
you keep
the formula handy (in a text file or sample workbook or just a link to
Chip's
site), it wouldn't be too hard to copy it into your worksheet.

But if you want...

Option Explicit
Function YrsMthsDays(rng1 As Range, rng2 As Range) As Variant

Dim iCtr As Long
Dim myVals(0 To 2) As Long
Dim myIntervals As Variant
Dim myStrs As Variant
Dim myOutput As String

myIntervals = Array("y", "ym", "md")
myStrs = Array(" years, ", " months, ", " days")

myOutput = ""
For iCtr = LBound(myVals) To UBound(myVals)
myVals(iCtr) = Application.Evaluate("datedif(" _
& rng1.Address(external:=True) _
& "," & rng2.Address(external:=True) _
& ",""" & myIntervals(iCtr) & """)")
myOutput = myOutput & myVals(iCtr) & myStrs(iCtr)
Next iCtr

YrsMthsDays = myOutput

End Function

Rob wrote:

Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years
and
1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete
months -
for
school report purposes. I can get Years and months but not
accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th
December - I
get 9 years and 1 month.


--

Dave Peterson