Conditional Formatting for due dates

[MAI2010]

Hi all,

I am trying to use conditional formatting to fill the "assignment status" cell orange 2 days prior to the due date and red on the due date. I have added formulas to change the due dates based on the start date, as some people will start on different dates. I would like to be able to replicate this for about 10-15 columns. Can someone please explain to me how to complete this?

Please see example worksheet.

Thank you very much!

[Max, Singapore]

1. Apply CF on cell C8 using the formulas below

Condition 1: =AND(C$4-TODAY()>0,C$4-TODAY()<=2)
Format fill > Orange

Condition 2: =C$4-TODAY()<=0
Format fill > Red

2. Use Format Painter to "copy" the CF in C8 to other cells in the same column. Select C8, do a double-click on the Format Painter button. With the double-click, you can then multiple click on cells C12, then on C16, etc to copy the CF to all these cells. Press Esc when complete to revert the cursor to normal

3. Use Format Painter to "copy" the CF in Col C to other columns. Select column C (click on the column header), double-click on the Format Painter button.Then just select the column headers for D, E, F, etc to similarly copy the CF to the other columns. Press Esc when complete to revert the cursor to normal

-------------------------------------
Success? Celebrate it, click the little star at the bottom left of my responses

[FDibbins]

Unless you have some way to "turn off" the CF, the red will never change, after today's date is passed?

Also, a slightly quicker way to apply the CF in 1 go...
1. highlight the range you want to apply the conditional formatting to (I used =$C$5:$D$49)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =AND($B5="Assignment Status ",C3+2>=TODAY()) format fill ORANGE
repeat 3 & 4 with ...
=AND($B5="Assignment Status ",C3>=TODAY()) format fill RED

[Mazzaropi]

Good evening.

First of all, I suggest you to eliminate Line 7 (DISREGARD) and the others the same.
You don´t need to use an unnecessary data processing.

Follow this:

• Change formula at C8 (Assingment status)

Before: =IF(C7>=2;"OK";"Due")
Now...: =IF(C6<=TODAY();"Due";"OK")

• Adapt the well done Conditional Formatting formulas from Mr. "Max, Singapore" to your reality. They work perfectly.

As you need a lot of controls, eliminating the unnecessary process, can save time.

This is my suggestion.

I hope it helps.

Please, tell us if it worked for you.

Greetings from Brazil.

[MAI2010]

Thank you all for the help! I am, however, having an issue with the future dates being highlighted red. Is there a way to only make the expired dates red, 2 days prior orange, and the future dates left alone?

Thank you for your time!

[FDibbins]

Unless you have some way to "turn off" the CF, the red will never change, after today's date is passed?

Thats why I asked the above

If you could have some "code" that you entered in a cell when you no longer need to see the CF, we could use that to "turn off" the color?

[MAI2010]

I tried

but it didn't work...any suggestions?

[FDibbins]

This is a sample from your table...

	A	B	C	D
3			Name	Name
4		Start Date	10/1/2013	10/1/2013
5	Week 1	Assignment Name
6		Due Date	10/8/2013	10/8/2013
7		Disregard	##########	##########
8		Assignment Status	Due	Due

Perhaps you could put "Received" or something in the Assignment Status row? We could then to the code to monitor for that?

[Max, Singapore]

To avoid Excel calculating blanks as zeros
you can use this amendment in Condition 2 of my earlier response:
=AND(C$4<>"",C$4-TODAY()<=0)
The above will exclude blank cells (meant for "future" dates) from the CF's evaluation

p/s: Condition 1 can remain unchanged
-----------------------------
Any worth? Wave it, hit the little star at the bottom left of my responses