Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Age Formula
hi all,
If I am using the formula below to calculate the age when entering the date of birth in column A:A =DATEDIF(A:A,TODAY(),"y") & "Y " & DATEDIF(A:A,TODAY(),"ym") & "M " & DATEDIF(A:A, TODAY(), "md") & "D" The age then appears as in: 9Y 3M 15D I need to write 2 formulas in the end of that age column. One to count the number of children who are under 3Y 0M 0D, and one for children who are between 3Y 0M 0D and 10Y 0M 0D (inclusive). Any ideas? cheers, Tendresse |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Age Formula
One way:
J1: =COUNTIF(A:A,"" & DATE(YEAR(TODAY())-3, MONTH(TODAY()), DAY(TODAY()))) J2: =COUNTIF(A:A,"=" & DATE(YEAR(TODAY())-10, MONTH(TODAY()), DAY(TODAY()))) - J1 In article , Tendresse wrote: hi all, If I am using the formula below to calculate the age when entering the date of birth in column A:A =DATEDIF(A:A,TODAY(),"y") & "Y " & DATEDIF(A:A,TODAY(),"ym") & "M " & DATEDIF(A:A, TODAY(), "md") & "D" The age then appears as in: 9Y 3M 15D I need to write 2 formulas in the end of that age column. One to count the number of children who are under 3Y 0M 0D, and one for children who are between 3Y 0M 0D and 10Y 0M 0D (inclusive). Any ideas? cheers, Tendresse |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Age Formula
Just a note on your technique:
DateDif assumes that months, using the "md" switch, are as long as the starting month, so that if the value of the cell in column A is, say A1: 31 Jan 2005 then on 1 March 2008, the result will be 3Y 1M -1D and on 2 March 2008, the result will be 3Y 1M 0D which may not be what you're expecting... In article , Tendresse wrote: If I am using the formula below to calculate the age when entering the date of birth in column A:A =DATEDIF(A:A,TODAY(),"y") & "Y " & DATEDIF(A:A,TODAY(),"ym") & "M " & DATEDIF(A:A, TODAY(), "md") & "D" The age then appears as in: 9Y 3M 15D |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Age Formula
ummm .. so how do I go around that?
thanks a million for your reply .. "JE McGimpsey" wrote: Just a note on your technique: DateDif assumes that months, using the "md" switch, are as long as the starting month, so that if the value of the cell in column A is, say A1: 31 Jan 2005 then on 1 March 2008, the result will be 3Y 1M -1D and on 2 March 2008, the result will be 3Y 1M 0D which may not be what you're expecting... In article , Tendresse wrote: If I am using the formula below to calculate the age when entering the date of birth in column A:A =DATEDIF(A:A,TODAY(),"y") & "Y " & DATEDIF(A:A,TODAY(),"ym") & "M " & DATEDIF(A:A, TODAY(), "md") & "D" The age then appears as in: 9Y 3M 15D |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Age Formula
Hard to say (which is why I mentioned it to note rather than as an
error). Because months are rather fuzzy concepts, it depends on what you want. For example: Assume the birthday is 31 January 2006. Does 28 February 2008 result in 2Y 1M 0D, or 2Y 0M 28D? If it's the former, is 29 February 2008 *also* 2Y 1M 0D? In article , Tendresse wrote: ummm .. so how do I go around that? thanks a million for your reply .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|