Nested IF Statement Using Dates

[RobSDSU]

I have and Excel 2003 document with two columns of data. Column A holds the
date an event occurred (mm/dd/yyyy formatted as a date). Column B holds text
indicating the outcome of the event.

I need to find a total of each specific outcome during a specific period of
time (for instance during December 2006 and January 2007).

I'm trying to identify each record that meets certain criteria and if it
does, I'll put a 1 in Column C for one outcome, Column D for another outcome,
etc. Then I'll total the columns and have totals for each outcome.

I've tried the following nested IF statement in Column C but Excel doesn't
seem to recognize the date parameters properly:

=IF(A1<11/30/2006,0,IF(A1>2/1/2007,0,IF(B1="NOT ELIGIBLE",1,0)))

Is there a special way I should be entering the date in the expression? Or
is there another expression that could be used? Writing custom VBA code is
not an option.

Thank You!

Rob

 

[Bob Greenblatt]

I have and Excel 2003 document with two columns of data. Column A holds the
date an event occurred (mm/dd/yyyy formatted as a date). Column B holds text
indicating the outcome of the event.

I need to find a total of each specific outcome during a specific period of
time (for instance during December 2006 and January 2007).

I'm trying to identify each record that meets certain criteria and if it
does, I'll put a 1 in Column C for one outcome, Column D for another outcome,
etc. Then I'll total the columns and have totals for each outcome.

I've tried the following nested IF statement in Column C but Excel doesn't
seem to recognize the date parameters properly:

=IF(A1<11/30/2006,0,IF(A1>2/1/2007,0,IF(B1="NOT ELIGIBLE",1,0)))

Is there a special way I should be entering the date in the expression? Or
is there another expression that could be used? Writing custom VBA code is
not an option.

Thank You!

Rob

Rob,

First, you have posted this question about Excel 2003 to a Macintosh Excel
group. In this case, it does not matter, but it always helps to post to the
appropriate group.

To make sure Excel is unambiguous about dates, you should use the Datevalue
function. However, I think you may be having trouble understanding If
statements. Assuming your formula is in Cell C1, your formula reads: If A1
is before November 30 2006, make C1 a 0, but if A1 is later than February 1,
2007, also make C1= 0, if not check if B1 ="Not eligible", and make C1 a 1,
otherwise make it a 0. Is this really what you want? What if A1 is 1/1/07?

You also need to understand that a formula can only return a value to the
cell in which it resides, it can NOT change any other cell. So, If you want
to put a value in C1, based on an outcome in B1 if A1 is within a certain
date range, your formula in C1 should look like this:
=if(and(a1>datevalue("11/30/2006"),a1<datevalue("2/1/2007"),b1<>"Not
Eligible"),1,0).

If you want your value in C1 and D1 etc. to be based on different values of
B1, then you probably need some more intermediate columns to make this
easier to understand.

 

[JE McGimpsey]

I have and Excel 2003 document with two columns of data. Column A holds the
date an event occurred (mm/dd/yyyy formatted as a date). Column B holds text
indicating the outcome of the event.

I need to find a total of each specific outcome during a specific period of
time (for instance during December 2006 and January 2007).

I'm trying to identify each record that meets certain criteria and if it
does, I'll put a 1 in Column C for one outcome, Column D for another outcome,
etc. Then I'll total the columns and have totals for each outcome.

If you'd rather do it in one function (rather than totalling a column),
try:

=SUMPRODUCT(--(A1:A1000>=DATE(2006, 12, 1)), --(A1:A1000<=DATE(2007,
1, 31), --(B1:B1000="specific outcome))

You can see why this works at

http://www.mcgimpsey.com/excel/doubleneg.html

 

[square_cube]

=IF(A1<11/30/2006,0,IF(A1>2/1/2007,0,IF(B1="NOT ELIGIBLE",1,0)))

Is there a special way I should be entering the date in the expression?

Yes. Use the numerical value for the date. By default on a Mac this
starts with 1/1/1904 as day 0, on Windz its 1/1/1900 as day 0.

Assuminmg you are on a Mac (this is the Mac XL group...) your formula
should read

=IF(A1<37589,0,IF(A1>37652,0,IF(B1="NOT ELIGIBLE",1,0)))

Alternatively you could use the Datevalue function (this should get
over cross platform difficulties)

=IF(A1<DATEVALUE("11/30/2006"),0,IF(A1>DATEVALUE("2/1/2007"),
0,IF(B1="NOT ELIGIBLE",1,0)))

 

[RobSDSU]

Thank you Bob.......your response solved the problem for me.

Rob

 

[RobSDSU]

WOW! Never heard of a unary minus operator before.

Thanks for the alternative.

Rob