Complex Formula question
 

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

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

Complex Formula question
 

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

Complex Formula question
 

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

Complex Formula question
 

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

Complex Formula question
 

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

Complex Formula question
 

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

Complex Formula question
 

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

Complex Formula question
 

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

Complex Formula question
 

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