A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Calculate 'exact' age in EXCEL



 
 
Thread Tools Display Modes
  #1  
Old January 9th 09, 07:35 PM posted to microsoft.public.excel.worksheet.functions
Zanny Garbett
external usenet poster
 
Posts: 1
Default 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
Ads
  #2  
Old January 9th 09, 08:16 PM posted to microsoft.public.excel.worksheet.functions
Mike H[_3_]
external usenet poster
 
Posts: 39
Default 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  
Old January 9th 09, 10:40 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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
>>
>> EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com/default.aspx?ref=ng

>

  #4  
Old January 9th 09, 11:04 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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  
Old January 10th 09, 12:50 PM posted to microsoft.public.excel.worksheet.functions
Niek Otten
external usenet poster
 
Posts: 3,440
Default 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

 




Thread Tools
Display Modes

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

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 07:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.