Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
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

  #2   Report Post  
Old 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
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   Report Post  
Old 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
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(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   Report Post  
Old 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
Default 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   Report Post  
Old 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
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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:16 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017