Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate 'exact' age in EXCEL
Try this with:
[A1] = date of birth; [B1] = Date of death; =DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"& DATEDIF(A1,B1,"md")&"d" Should work OK Regards Zanny EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate 'exact' age in EXCEL
Hi,
I've never understood the value of months in calculating a time period. Try these 2 dates with your formula and you will get 7y,1m,-2d a1= 31/01/2002 b1= 01/03/2009 It returns this type of result if the first month is longer than the second. Mike On Jan 10, 6:35*am, Zanny Garbett wrote: Try this with: [A1] = date of birth; [B1] = Date of death; =DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"& DATEDIF(A1,B1,"md")&"d" Should work OK Regards Zanny EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com/default.aspx?ref=ng |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate 'exact' age in EXCEL
Mike H wrote:
Hi, I've never understood the value of months in calculating a time period. I agree. This should be sufficient: =DATEDIF(A1,B1,"y")&"y, "&DATEDIF($A$1,$B$1,"yd")&"d" If you insist on months, try this: =IF(B1A1,DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "& IF(DAY(A1)DAY(B1-DAY(B1)),DAY(B1),DAY(B1)+DAY(B1-DAY(B1))-DAY(A1))&"d",NA()) although you will get the same answer for the difference between January 28th-31st and any date after February. Try these 2 dates with your formula and you will get 7y,1m,-2d a1= 31/01/2002 b1= 01/03/2009 It returns this type of result if the first month is longer than the second. Not exactly, considering both January and March have 31 days...it is February that is throwing off the calculation. The difference between the 31st day of February in 2009 (evaluates as March 3rd) and the first day of March in 2009 is -2. Mike On Jan 10, 6:35 am, Zanny Garbett wrote: Try this with: [A1] = date of birth; [B1] = Date of death; =DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"& DATEDIF(A1,B1,"md")&"d" Should work OK Regards Zanny EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com/default.aspx?ref=ng |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate 'exact' age in EXCEL
Glenn wrote:
If you insist on months, try this: =IF(B1A1,DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "& IF(DAY(A1)DAY(B1-DAY(B1)),DAY(B1),DAY(B1)+DAY(B1-DAY(B1))-DAY(A1))&"d",NA()) Actually, there is something wrong with this...not sure what and no time to figure it out right now. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate 'exact' age in EXCEL
What is your question?
-- Kind regards, Niek Otten Microsoft MVP - Excel "Zanny Garbett" wrote in message ... Try this with: [A1] = date of birth; [B1] = Date of death; =DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"& DATEDIF(A1,B1,"md")&"d" Should work OK Regards Zanny EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 | Excel Discussion (Misc queries) | |||
Formula to calculate someone's exact age | Excel Discussion (Misc queries) | |||
How do I calculate exact difference between two dates in y,m,d. | Excel Worksheet Functions | |||
How do I calculate exact difference between two dates in y,m,d. | Excel Worksheet Functions | |||
How to get the Excel to calculate the exact date from a given date | Excel Worksheet Functions |