Conditional Formatting

tennis55

New member
Joined
May 15, 2011
Messages
2
Reaction score
0
Points
0
I'm trying to create conditional formatting that will do the following:
A3= today's date I10:


Anything due before today's date= Red
Due within the next week: Anything due within 5 business days of A3= Yellow
Due in the future: Anything due beyond 5 business days of A3= Green

I've created the following conditional formatting rules:
=IF($A$3>WORKDAY(I10,2,$M$5:$M$6),TRUE,FALSE) [will be red]
=IF($A$3>=WORKDAY(I10,2,$M$5:$M$7),TRUE,FALSE) [will be yellow]
=IF($A$3<=WORKDAY(I10,6,$M$5:$M$6),TRUE,FALSE) [will be green]

I also need a way to count the amount of reds, yellows, and greens, without using a macro. I have used this formula, but it's not working properly: =IF(J10="","",IF(J10="Complete",4,IF(J10<$A$3,1,IF(J10=$A$3+(WORKDAY(I10,6,$M$5:$M$6)),3))))) Upon receipt, I will give the file.

Any help?
 
The site doesn't specify how to include only workdays in the function. I want to only include workdays and holidays.

Also it doesn't specify how to break out the days. For example:

Anything due before today's date= Red
Due within the next week: Anything due within 5 business days of A3= Yellow
Due in the future: Anything due beyond 5 business days of A3= Green

How do I do that?
 
Hi

Just substitute your formulae for the ones Bob uses in his example.
You don't need the whole of your formulae however, miss out the IF statements
=$A$3>WORKDAY(I10,2,$M$5:$M$6) [will be red]
=$A$3>=WORKDAY(I10,2,$M$5:$M$7) [will be yellow]
=$A$3<=WORKDAY(I10,6,$M$5:$M$6) [will be green]

The formulae themselves will return a True or False which the CF will use to determine whether to colour the cell.
 
Back
Top