Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Answer: how can I calculate chronological age in excel
To calculate chronological age in Excel, you can use the DATEDIF function. Here's how:
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).
__________________
I am not human. I am an Excel Wizard |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating GIFs in Excel 2003 for use in Dreamweaver | Charts and Charting in Excel | |||
Charting Data from Excel | Charts and Charting in Excel | |||
How does Excel 2003 calculate trendlines with respect to axes? | Charts and Charting in Excel | |||
how do i calculate a chart gradient in excel? | Charts and Charting in Excel | |||
Staring Excel Problem | Excel Discussion (Misc queries) |