If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Calculate 'exact' age in EXCEL
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Calculate 'exact' age in EXCEL

#1
January 9th 09, 06:35 PM posted to microsoft.public.excel.worksheet.functions
 Zanny Garbett external usenet poster 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, 07:16 PM posted to microsoft.public.excel.worksheet.functions
 Mike H[_3_] external usenet poster 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, 09:40 PM posted to microsoft.public.excel.worksheet.functions
 Glenn external usenet poster 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(B1>A1,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, 10:04 PM posted to microsoft.public.excel.worksheet.functions
 Glenn external usenet poster Posts: 1,240
Calculate 'exact' age in EXCEL

Glenn wrote:
>
> If you insist on months, try this:
>
> =IF(B1>A1,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, 11:50 AM posted to microsoft.public.excel.worksheet.functions
 Niek Otten external usenet poster 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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 [email protected] Excel Discussion (Misc queries) 0 June 11th 08 11:30 PM Formula to calculate someone's exact age NavyPianoMan Excel Discussion (Misc queries) 6 August 28th 07 07:30 PM How do I calculate exact difference between two dates in y,m,d. Muhammad Javaid Hassan Excel Worksheet Functions 8 September 23rd 06 10:02 PM How do I calculate exact difference between two dates in y,m,d. Muhammad Javaid Hassan Excel Worksheet Functions 3 September 19th 06 01:42 PM How to get the Excel to calculate the exact date from a given date Phil Excel Worksheet Functions 10 April 28th 06 07:07 PM

All times are GMT +1. The time now is 11:42 AM.