Date forecasting with formatting

[BlueDingo]

Today is 14-Nov-12 Hello Forum, appreciate some help with this headache

The office has a large spreadsheet where Column A has a "start date" and Column B "Clearance Date"
We would like to see a red font for those dates that have passed the current date.
A yellow cell for dates that are within a 28 day period. That is there is a 28 day grace period to have processing done.
If at all possible is there a way of formatting (over-riding Column A) font to green and white background when a date is input to Column B?

Maybe I need hidden columns with other formulas?

[TABLE="width: 204"]
<TBODY>[TR]
[TD="class: xl22, width: 32, bgcolor: transparent"][/TD]
[TD="class: xl23, width: 122, bgcolor: transparent"]A
[/TD]
[TD="class: xl22, width: 117, bgcolor: transparent"]B
[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]1
[/TD]
[TD="class: xl24, width: 122, bgcolor: transparent"]Start
Date
[/TD]
[TD="class: xl25, width: 117, bgcolor: transparent"]Clearance
Signed
(28 day grace)
[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]2
[/TD]
[TD="class: xl26, bgcolor: transparent"]02-May-12
[/TD]
[TD="class: xl23, bgcolor: transparent"]01-Jun-12
[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]3
[/TD]
[TD="class: xl27, bgcolor: transparent"]30-May-12
[/TD]
[TD="class: xl23, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]4
[/TD]
[TD="class: xl27, bgcolor: transparent"]28-Jun-12
[/TD]
[TD="class: xl23, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]5
[/TD]
[TD="class: xl28, bgcolor: yellow"]14-Nov-12
[/TD]
[TD="class: xl23, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]6
[/TD]
[TD="class: xl23, bgcolor: transparent"]17-Nov-12
[/TD]
[TD="class: xl23, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]7
[/TD]
[TD="class: xl23, bgcolor: transparent"]14-Jan-13
[/TD]
[TD="class: xl23, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]8
[/TD]
[TD="class: xl23, bgcolor: transparent"]14-Jan-13
[/TD]
[TD="class: xl23, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

Thank you, Pete

 

[calvin771]

Conditional Formatting can solve your headache!

You have three conditions that you're checking for:

If adjacent cell is > 0, format with green text
If cell is less than today, format with red text
If cell is within 28 days of today, format with a yellow fill & normal text


Select the fist date in your spreadsheet (cell A2 in the example you posted) and create the following rules in conditional formatting choosing the ways you want each one formatted (same order as above):

=b2>0
=a2<today()
=a2<(today()-28)

Set the range for your new rules from A2 to the last entry in the column and the dates should format correctly.