IF THEN Function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a 9x2 matrix for which I need to create and AND/OR function based on
values in column A ("Complete", "In Progress", "Past Due") & values in
column B ("< Today()", "=Today()", ">Today()"). Please see the actual grid
below:

Complete < Today()
Complete = Today()
Complete > Today()
In Progress < Today()
In Progress = Today()
In Progress > Today()
Past Due < Today()
Past Due = Today()
Past Due > Today()

Here's what I'm trying to achieve (using conditional formatting "green" = 1,
"yellow" = 2, "red" = 3).

Column A = Status; Column B = Due Dates. Here are the business rules:

- If column A = "Complete" then I can ignore the "Due Date"; hence the value
should be 1 (or "green")
- If column A = "Past Due" then the can ignore the "Due Date"; hence the
value should always be 3 (or "red")
- HOWVER, if column A = "In Progress" then I DO need to look at date value
in Column B. Hence; I will have 3 scenarios under this:

In Progress AND < Today() = "Red" (or 3)
In Progress AND = Today() = "Yellow" (or 2)
In Progress AND > Today() = "Green" (or 1)


I hope this make sense!?!? Again, I appreciate if anyone knows how to
write such function.

Thanks in advance,
Tom
 
Tom,

Goto menu Format>Conditional Formatting

Change Condition1 to Formula Is
Add the formula
=OR($A1="Past Due",AND($A1="In Progress",$B1<TODAY()))
Click the Format button, select the Pattern tab, and set the colour to red
OK

Click the Add >> button
Change Condition2 to Formula Is
Add the formula
=OR($A1="Complete",AND($A1="In Progress",$B1>TODAY()))
Click the Format button, select the Pattern tab, and set the colour to green
OK

Click the Add >> button
Change Condition3 to Formula Is
Add the formula
=AND($A1="In Progress",$B1=TODAY())
Click the Format button, select the Pattern tab, and set the colour to
yellow
OK
OK

That's it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top