How to do multiple IF checks with dates AND text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RJ Fleener - 18 Oct 2006 16:58 GMT

Hello All,

I am trying to get a formula set up that will look at multiple colums, and
produce output in a particular column based on the checks made. It's a
boolean thing; here's an example:

In column A, I have dates in the format mm/dd/yy, in column B, I have text
that can be one of four selections: Resolved, In Progress, Assigned, New. I
need to compare column A against:

1. Today's date. If column A is earlier than today's date, AND column B is
"Resolved", I need to set column C to "No."
2. Today's date. If column A is later than today's date, AND column B is
anything but "Resolved", I need to set column C to "Yes".
3. Today's date. If column A is later than today's date, and column B is
"Resolved", I need to set column C to "No".
4. Today's date. If column A is later than today's date, and column B is
anything but "Resolved", I need to set column C to "No".

I've tried multiple variations of the IF, AND, OR worksheet functions, and
either it sets them all to the same thing (60 rows in column C) or gives me a
zero in column C. Also thought of using VLOOKUP, but could not make that
work either. Can sombody help me figure out the necessary cell formula that
goes in column C, please? Thanks in advance!

Signature

- RJF

Reply to this Message

joeu2004@hotmail.com - 18 Oct 2006 17:20 GMT

> In column A, I have dates in the format mm/dd/yy, in column B, I have text
> that can be one of four selections: Resolved, In Progress, Assigned, New. I
[quoted text clipped - 8 lines]
> 4. Today's date. If column A is later than today's date, and column B is
> anything but "Resolved", I need to set column C to "No".

What if A is earlier than today's date AND B is not "Resolved"? I
assume you want "No".

=if(and(B1<>"Resolved", A1>today()), "Yes", "No")

Reply to this Message

RJ Fleener - 21 Oct 2006 06:30 GMT

> > In column A, I have dates in the format mm/dd/yy, in column B, I have text
> > that can be one of four selections: Resolved, In Progress, Assigned, New. I
[quoted text clipped - 13 lines]
>
> =if(and(B1<>"Resolved", A1>today()), "Yes", "No")

Yes, that is correct. I guess I just forgot that one. I will give this a
go and see if it works...thanks!
--
RJF

Reply to this Message

RJ Fleener - 22 Oct 2006 03:41 GMT

> > > In column A, I have dates in the format mm/dd/yy, in column B, I have text
> > > that can be one of four selections: Resolved, In Progress, Assigned, New. I
[quoted text clipped - 18 lines]
> --
> RJF

Follow-up: Your solution works like it should; thanks for the assist!

Signature

- RJF

Reply to this Message

Dave F - 18 Oct 2006 17:28 GMT

1.
=IF(AND(A1<TODAY(),B1="Resolved"),"No",IF(AND(A1>TODAY(),B1<>"Resolved"),"YES",IF(AND(A1>TODAY(),B1="Resolved"),"No",IF(AND(A1>TODAY(),B1<>"Resolved")),"No",""))))

Dave

Signature

Brevity is the soul of wit.

> Hello All,
>
[quoted text clipped - 20 lines]
> work either. Can sombody help me figure out the necessary cell formula that
> goes in column C, please? Thanks in advance!

Reply to this Message

RJ Fleener - 21 Oct 2006 06:33 GMT

> 1.
> =IF(AND(A1<TODAY(),B1="Resolved"),"No",IF(AND(A1>TODAY(),B1<>"Resolved"),"YES",IF(AND(A1>TODAY(),B1="Resolved"),"No",IF(AND(A1>TODAY(),B1<>"Resolved")),"No",""))))
[quoted text clipped - 25 lines]
> > work either. Can sombody help me figure out the necessary cell formula that
> > goes in column C, please? Thanks in advance!

I'll give this a try and see if it works out...thanks!

Signature

- RJF

Reply to this Message

RJ Fleener - 22 Oct 2006 03:42 GMT

> > 1.
> > =IF(AND(A1<TODAY(),B1="Resolved"),"No",IF(AND(A1>TODAY(),B1<>"Resolved"),"YES",IF(AND(A1>TODAY(),B1="Resolved"),"No",IF(AND(A1>TODAY(),B1<>"Resolved")),"No",""))))
[quoted text clipped - 27 lines]
>
> I'll give this a try and see if it works out...thanks!

Follow-up: Your solution works; thanks for the assist!

Signature

- RJF

Reply to this Message