Remember Me?

#1
January 9th 09, 07:35 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 1
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

#2
January 9th 09, 08:16 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2007 Posts: 39
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

#3
January 9th 09, 10:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240
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

#4
January 9th 09, 11:04 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240
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
January 10th 09, 12:50 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,440
Calculate 'exact' age in EXCEL

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Discussion (Misc queries) 0 June 11th 08 11:30 PM NavyPianoMan Excel Discussion (Misc queries) 6 August 28th 07 07:30 PM Muhammad Javaid Hassan Excel Worksheet Functions 8 September 23rd 06 10:02 PM Muhammad Javaid Hassan Excel Worksheet Functions 3 September 19th 06 01:42 PM Phil Excel Worksheet Functions 10 April 28th 06 07:07 PM

All times are GMT +1. The time now is 11:16 PM.