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!!

"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!!

If you cannot use EDATE()

EDATE won't work on arrays.

....(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th anniversary
of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula, replacing C5
with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date. Suppose
that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into Y5 and
copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And again,
X1 can be replaced with TODAY(), if that is what the OP truly wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

Yeah, I would use a helper column to get the age in years. Leap day is real
pita!

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th
anniversary of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula, replacing
C5 with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date.
Suppose that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into
Y5 and copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And
again, X1 can be replaced with TODAY(), if that is what the OP truly
wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

Thankfully, what I'm needing this for is just fine considering someone 18 at
the 3/1 date. For that situation, would I just do the initial (non-Edate)
formula suggested:
=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*(F5:F14<=DATE(2009, 10,
31))*(Y5:Y14X1), D5:D14)
?


"T. Valko" wrote:

Yeah, I would use a helper column to get the age in years. Leap day is real
pita!

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th
anniversary of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula, replacing
C5 with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date.
Suppose that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into
Y5 and copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And
again, X1 can be replaced with TODAY(), if that is what the OP truly
wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

Ok, So I tried the following formula and still ended up with a value error:
=SUMPRODUCT((F5:F14=DATE(2009,7,1))*(F5:F14<=DATE (2009,10,31))*(DATE(18+YEAR(C5:C14),MONTH(C5:C14), DAY(C5:C14))X1,D5:D14)

Since the whole situation with leap year doesn't matter in this situation,
is there a basic formula I can insert into my original larger formula that
replaces the <18? I have several similar formulas to the original one and all
of them require that I change the term "18" to instead reflect birthdates
(some require less than 18, others require greater than or equal to 18). So,
adding in a separate column isn't really a possibility, and I have no idea
what Helper Cells are.

"JoeU2004" wrote:

"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th anniversary
of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula, replacing C5
with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date. Suppose
that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into Y5 and
copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And again,
X1 can be replaced with TODAY(), if that is what the OP truly wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

the whole situation with leap year doesn't matter
in this situation
adding in a separate column isn't really a possibility
and I have no idea what Helper Cells are.

A helper cell is a cell that holds an intermediate calculation. If I were
you I'd use a column of helper cells to calculate the age. Then you'd just
reference that range of helper cells to test for the age.

For example, A1:A10 = birth dates. To calculate the age as of today's date
enter this formula in B1 and copy down to B10:

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

If you want to use a specific target date then either enter that date in a
cell and replace NOW() with the cell reference or use that date directly in
the formula.

C1 = 1/1/2009

=DATEDIF(A1,C1,"y")
=DATEDIF(A1,DATE(2009,1,1),"y")

--
Biff
Microsoft Excel MVP


"MSW Intern" wrote in message
...
Ok, So I tried the following formula and still ended up with a value
error:
=SUMPRODUCT((F5:F14=DATE(2009,7,1))*(F5:F14<=DATE (2009,10,31))*(DATE(18+YEAR(C5:C14),MONTH(C5:C14), DAY(C5:C14))X1,D5:D14)

Since the whole situation with leap year doesn't matter in this situation,
is there a basic formula I can insert into my original larger formula that
replaces the <18? I have several similar formulas to the original one and
all
of them require that I change the term "18" to instead reflect birthdates
(some require less than 18, others require greater than or equal to 18).
So,
adding in a separate column isn't really a possibility, and I have no idea
what Helper Cells are.

"JoeU2004" wrote:

"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th
anniversary
of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula, replacing
C5
with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date.
Suppose
that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into Y5 and
copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And
again,
X1 can be replaced with TODAY(), if that is what the OP truly wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

Ok, so I input a column of helper cells and the following formula:
=DATEDIF(C7,DATE(2009,10,1),"18")
which returned a number error. I'm really needing to get these reports
generated for the quarter, which ends tomorrow. I'm not sure what I'm doing
wrong, but I need it to either calculate by quarters or preferably to
calculate based on the current date (updating the date by itself). What am I
doing wrong? How can I make this formula issue work?!

"T. Valko" wrote:

the whole situation with leap year doesn't matter
in this situation
adding in a separate column isn't really a possibility
and I have no idea what Helper Cells are.

A helper cell is a cell that holds an intermediate calculation. If I were
you I'd use a column of helper cells to calculate the age. Then you'd just
reference that range of helper cells to test for the age.

For example, A1:A10 = birth dates. To calculate the age as of today's date
enter this formula in B1 and copy down to B10:

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

If you want to use a specific target date then either enter that date in a
cell and replace NOW() with the cell reference or use that date directly in
the formula.

C1 = 1/1/2009

=DATEDIF(A1,C1,"y")
=DATEDIF(A1,DATE(2009,1,1),"y")

--
Biff
Microsoft Excel MVP


"MSW Intern" wrote in message
...
Ok, So I tried the following formula and still ended up with a value
error:
=SUMPRODUCT((F5:F14=DATE(2009,7,1))*(F5:F14<=DATE (2009,10,31))*(DATE(18+YEAR(C5:C14),MONTH(C5:C14), DAY(C5:C14))X1,D5:D14)

Since the whole situation with leap year doesn't matter in this situation,
is there a basic formula I can insert into my original larger formula that
replaces the <18? I have several similar formulas to the original one and
all
of them require that I change the term "18" to instead reflect birthdates
(some require less than 18, others require greater than or equal to 18).
So,
adding in a separate column isn't really a possibility, and I have no idea
what Helper Cells are.

"JoeU2004" wrote:

"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th
anniversary
of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula, replacing
C5
with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date.
Suppose
that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into Y5 and
copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And
again,
X1 can be replaced with TODAY(), if that is what the OP truly wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!

=DATEDIF(C7,DATE(2009,10,1),"18")

Try it like this:

=DATEDIF(C7,DATE(2009,10,1),"y")

That will return the age in years.

--
Biff
Microsoft Excel MVP


"MSW Intern" wrote in message
...
Ok, so I input a column of helper cells and the following formula:
=DATEDIF(C7,DATE(2009,10,1),"18")
which returned a number error. I'm really needing to get these reports
generated for the quarter, which ends tomorrow. I'm not sure what I'm
doing
wrong, but I need it to either calculate by quarters or preferably to
calculate based on the current date (updating the date by itself). What am
I
doing wrong? How can I make this formula issue work?!

"T. Valko" wrote:

the whole situation with leap year doesn't matter
in this situation
adding in a separate column isn't really a possibility
and I have no idea what Helper Cells are.

A helper cell is a cell that holds an intermediate calculation. If I were
you I'd use a column of helper cells to calculate the age. Then you'd
just
reference that range of helper cells to test for the age.

For example, A1:A10 = birth dates. To calculate the age as of today's
date
enter this formula in B1 and copy down to B10:

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

If you want to use a specific target date then either enter that date in
a
cell and replace NOW() with the cell reference or use that date directly
in
the formula.

C1 = 1/1/2009

=DATEDIF(A1,C1,"y")
=DATEDIF(A1,DATE(2009,1,1),"y")

--
Biff
Microsoft Excel MVP


"MSW Intern" wrote in message
...
Ok, So I tried the following formula and still ended up with a value
error:
=SUMPRODUCT((F5:F14=DATE(2009,7,1))*(F5:F14<=DATE (2009,10,31))*(DATE(18+YEAR(C5:C14),MONTH(C5:C14), DAY(C5:C14))X1,D5:D14)

Since the whole situation with leap year doesn't matter in this
situation,
is there a basic formula I can insert into my original larger formula
that
replaces the <18? I have several similar formulas to the original one
and
all
of them require that I change the term "18" to instead reflect
birthdates
(some require less than 18, others require greater than or equal to
18).
So,
adding in a separate column isn't really a possibility, and I have no
idea
what Helper Cells are.

"JoeU2004" wrote:

"T. Valko" wrote:
EDATE won't work on arrays.

Busted! Thanks for pointing that out.

In light of that fact, I don't have a single-formula solution that
effectively does what EDATE would, namely ensuring that the 18th
anniversary
of a 2/29 date is 2/28, if that is what the OP wants.

The only method I can think of requires helper cells.

My normal non-EDATE workaround is something like:

MIN(DATE(18+YEAR(C5),MONTH(C5),DAY(C5)),
DATE(18+YEAR(C5),1+MONTH(C5),0)

But that does not lend itself to a SUMPRODUCT or array formula,
replacing
C5
with a range like C5:C14.

An alternative is to create a column for the 18th anniversary date.
Suppose
that's Y5:Y14. Put EDATE(C5,18*12) or the MIN formula above into Y5
and
copy down. Then the SUMPRODUCT formula becomes:

=SUMPRODUCT((F5:F14=DATE(2009, 7, 1))*
(F5:F14<=DATE(2009, 10, 31))*(Y5:Y14X1), D5:D14)

where, again, X1 might be =TODAY() or a recent evaluation date. And
again,
X1 can be replaced with TODAY(), if that is what the OP truly wants.


----- original message -----

"T. Valko" wrote in message
...
If you cannot use EDATE()

EDATE won't work on arrays.

...(EDATE(C5:C14,18*12)...

That'll return a #VALUE! error.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"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!!