Complex Formula question
"MSW Intern" wrote:
My current formula just uses <18, but that only works if I input
each person's age. I need to revise it so that it looks
at the birthdates in coorelation with the current date
If you are saying that C5:C14 will contain date of birth instead of age,
then:
=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(EDATE(C5:C14,18*12)X1), D5:D14)
where X1 might contain =TODAY(), or it might contain a specific date. You
can replace X1 with TODAY() if you wish.
(Usually we do not truly want "the current date", but instead some current
"evaluation date".)
I use EDATE() to be sure that a birthdate like 2/29/1992 is handled
correctly. I believe the person is considered 18 years on 2/28/2010, not
3/1/2010. If you get a #NAME error, see the EDATE help page.
If you cannot use EDATE(), then:
=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*
(DATE(18+YEAR(C5:C14),MONTH(C5:C14),DAY(C5:C14))X 1), D5:D14)
But that does have the problem(?) that 3/1/2010 will be considered 18 years
after 2/29/1992.
----- original message -----
"MSW Intern" wrote in message
...
Is there a way to have Excel consider a birth date to determine if the
person
is under 18 versus 18 and older? My current formula just uses <18, but
that
only works if I input each person's age. I need to revise it so that it
looks
at the birthdates in coorelation with the current date to determine which
category the person falls into.
My current formula reads:
=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*(F5:F14<=DATE(2009, 10,
31))*(C5:C14<18), D5:D14)
I need to revise the part that says <18 to instead consider birthdates,
while keeping all the rest of this long formula. Please help!!
|