Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate exact difference between two dates in y,m,d. | Excel Worksheet Functions | |||
How do I calculate exact difference between two dates in y,m,d. | Excel Worksheet Functions | |||
How to get the Excel to calculate the exact date from a given date | Excel Worksheet Functions | |||
Formula to compute someone's age if you enter their B-day | Excel Discussion (Misc queries) | |||
EXACT formula | Excel Discussion (Misc queries) |