Nested IF Statement Using Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RobSDSU - 30 Jul 2007 20:44 GMT

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

Reply to this Message

Bob Greenblatt - 31 Jul 2007 13:20 GMT

On 7/30/07 3:44 PM, in article
C9DDF6A9-FB0A-4F10-9F66-C43FF5CD90E0@microsoft.com, "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
[quoted text clipped - 19 lines]
>
> 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.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

JE McGimpsey - 31 Jul 2007 13:36 GMT

> 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
[quoted text clipped - 6 lines]
> 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

Reply to this Message

RobSDSU - 31 Jul 2007 17:56 GMT

WOW! Never heard of a unary minus operator before.

Thanks for the alternative.

Rob

> > 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
[quoted text clipped - 16 lines]
>
> http://www.mcgimpsey.com/excel/doubleneg.html

Reply to this Message

square_cube@hotmail.com - 31 Jul 2007 14:00 GMT

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

Reply to this Message

RobSDSU - 31 Jul 2007 16:58 GMT

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

Rob

> 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
[quoted text clipped - 19 lines]
>
> Rob

Reply to this Message

Mac Forum / Applications / Excel / July 2007

Nested IF Statement Using Dates