Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default Formula to calculate someone's exact age

I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's exact
age based on their birthdate and today's date (formatted military-style as
dd-mmm-yy). In the example below, the above formula produces an incorrect
answer because it seems to round up.
Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83) produces
the number "24", which is correct when you go by the year "yy", but actually
incorrect because this person isn't actually 24 years old until 17-Sep-07.
Why does EXCEL seem to round up?

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Formula to calculate someone's exact age

Look he

http://www.cpearson.com/excel/datedif.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
|I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's exact
| age based on their birthdate and today's date (formatted military-style as
| dd-mmm-yy). In the example below, the above formula produces an incorrect
| answer because it seems to round up.
| Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83) produces
| the number "24", which is correct when you go by the year "yy", but actually
| incorrect because this person isn't actually 24 years old until 17-Sep-07.
| Why does EXCEL seem to round up?
|


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Formula to calculate someone's exact age

Excel isn't rounding the result. You're subtracting the calander year
numbers 1983 from 2007. The months/days are ignored. To find the age using
full years try this:

=DATEDIF(birth_date,NOW(),"y")

See this for more info on DATEDIF:

http://cpearson.com/excel/datedif.htm

--
Biff
Microsoft Excel MVP


"NavyPianoMan" wrote in message
...
I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's
exact
age based on their birthdate and today's date (formatted military-style as
dd-mmm-yy). In the example below, the above formula produces an incorrect
answer because it seems to round up.
Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83)
produces
the number "24", which is correct when you go by the year "yy", but
actually
incorrect because this person isn't actually 24 years old until 17-Sep-07.
Why does EXCEL seem to round up?



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default Formula to calculate someone's exact age

It worked! Awesome! Thanks for the help--you made my day!
--NavyPianoMan

"T. Valko" wrote:

Excel isn't rounding the result. You're subtracting the calander year
numbers 1983 from 2007. The months/days are ignored. To find the age using
full years try this:

=DATEDIF(birth_date,NOW(),"y")

See this for more info on DATEDIF:

http://cpearson.com/excel/datedif.htm

--
Biff
Microsoft Excel MVP


"NavyPianoMan" wrote in message
...
I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's
exact
age based on their birthdate and today's date (formatted military-style as
dd-mmm-yy). In the example below, the above formula produces an incorrect
answer because it seems to round up.
Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83)
produces
the number "24", which is correct when you go by the year "yy", but
actually
incorrect because this person isn't actually 24 years old until 17-Sep-07.
Why does EXCEL seem to round up?




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default Formula to calculate someone's exact age

It worked! Awesome! Thanks for the help--you made my day!
--NavyPianoMan

"Niek Otten" wrote:

Look he

http://www.cpearson.com/excel/datedif.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
|I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's exact
| age based on their birthdate and today's date (formatted military-style as
| dd-mmm-yy). In the example below, the above formula produces an incorrect
| answer because it seems to round up.
| Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83) produces
| the number "24", which is correct when you go by the year "yy", but actually
| incorrect because this person isn't actually 24 years old until 17-Sep-07.
| Why does EXCEL seem to round up?
|





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Formula to calculate someone's exact age

YEAR(D6-E6) would be nearer.

YEAR produces an integer number, so when you did that on each of the dates
individually you threw away the month and day information. You'd need to do
the subtraction first, then convert from days to years. You might, however,
get small errors with leap years if you just use the YEAR function in that
way, so better to use =DATEDIF(E6,D6,"y").
--
David Biddulph

"NavyPianoMan" wrote in message
...
I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's
exact
age based on their birthdate and today's date (formatted military-style as
dd-mmm-yy). In the example below, the above formula produces an incorrect
answer because it seems to round up.
Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83)
produces
the number "24", which is correct when you go by the year "yy", but
actually
incorrect because this person isn't actually 24 years old until 17-Sep-07.
Why does EXCEL seem to round up?



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Formula to calculate someone's exact age

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"NavyPianoMan" wrote in message
...
It worked! Awesome! Thanks for the help--you made my day!
--NavyPianoMan

"T. Valko" wrote:

Excel isn't rounding the result. You're subtracting the calander year
numbers 1983 from 2007. The months/days are ignored. To find the age
using
full years try this:

=DATEDIF(birth_date,NOW(),"y")

See this for more info on DATEDIF:

http://cpearson.com/excel/datedif.htm

--
Biff
Microsoft Excel MVP


"NavyPianoMan" wrote in message
...
I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's
exact
age based on their birthdate and today's date (formatted military-style
as
dd-mmm-yy). In the example below, the above formula produces an
incorrect
answer because it seems to round up.
Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83)
produces
the number "24", which is correct when you go by the year "yy", but
actually
incorrect because this person isn't actually 24 years old until
17-Sep-07.
Why does EXCEL seem to round up?






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
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
Formula to compute someone's age if you enter their B-day Millerk Excel Discussion (Misc queries) 9 March 2nd 06 02:16 PM
EXACT formula Muks Excel Discussion (Misc queries) 4 July 29th 05 05:59 PM


All times are GMT +1. The time now is 04:17 PM.

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

About Us

"It's about Microsoft Excel"