Conditional Formatting for Variance - Forecasted and Actual Dates

[Danny Anibal]

I have a set of forecasted and actual dates for different projects.

I want to be able to apply conditional formatting to the actual dates that fall before the forecasted date (in green) and after the forecasted date (in red)

See the file here:

https://www.dropbox.com/s/5yu4u3hc8sqvhda/Dates Formatting.xlsx?dl=0

This is an example data set

[TABLE="width: 1164"]
<tbody>[TR]
[TD]Project name
[/TD]
[TD]Forecasted Date 1
[/TD]
[TD]Actual Date 1
[/TD]
[TD]Forecasted Date 2
[/TD]
[TD]Actual Date 2
[/TD]
[TD]Forecasted Date 3
[/TD]
[TD]Actual Date 3
[/TD]
[TD]Forecasted Date 4
[/TD]
[TD]Actual Date 4
[/TD]
[/TR]
[TR]
[TD]Project 1
[/TD]
[TD]17-Sep-14
[/TD]
[TD]13-Feb-15
[/TD]
[TD]1-Sep-15
[/TD]
[TD]1-Jun-15
[/TD]
[TD]16-Jun-15
[/TD]
[TD]25-May-15
[/TD]
[TD]12-Jun-15
[/TD]
[TD]3-Jun-15
[/TD]
[/TR]
</tbody>[/TABLE]


So it should look like this with the formatting

[TABLE="width: 1164"]
<tbody>[TR]
[TD]Project name
[/TD]
[TD]Forecasted Date 1
[/TD]
[TD]Actual Date 1
[/TD]
[TD]Forecasted Date 2
[/TD]
[TD]Actual Date 2
[/TD]
[TD]Forecasted Date 3
[/TD]
[TD]Actual Date 3
[/TD]
[TD]Forecasted Date 4
[/TD]
[TD]Actual Date 4
[/TD]
[/TR]
[TR]
[TD]Project 1
[/TD]
[TD]17-Sep-14
[/TD]
[TD]13-Feb-15 (red)
[/TD]
[TD]1-Sep-15
[/TD]
[TD]1-Jun-15 (green)
[/TD]
[TD]16-Jun-15
[/TD]
[TD]25-May-15 (green)
[/TD]
[TD]12-Jun-15
[/TD]
[TD]3-Jun-15 (green)
[/TD]
[/TR]
</tbody>[/TABLE]

Of course I have about pairs of forecasted and actual dates and about 80 on-going projects.

I’ve created a column with the variance values and have tried to create rules using the variances as reference for the formatting.

Where $BB$5:BB$74 is the column with the variance between the pair of forecasted and actual dates.

https://www.dropbox.com/s/loav24kniubx4cv/Capture3.PNG?dl=0

And where$H$5:$H$74 is the column of the Actual date, the column that I want the formatting on

https://www.dropbox.com/s/4pii639oyy61dd9/Capture4.PNG?dl=0

 

[RCBricker]

two rules for this

rule one:
=AND(C3 < B3,LEFT(C2,1)="A")
turns cell green

rule two:
=AND(C3>B3,LEFT(C2,1)="A")
turns cell red

 

[Danny Anibal]

Hi, thank you so much for your help

I must be doing something wrong because your formulas are not having any effect

I think it has to do with the cell references

Please see the screenshot

https://www.dropbox.com/s/igo9winn84cj1ip/Capture 1a.JPG?dl=0