Changing a cell color based on a date comparison

[soozy]

Hello

Am new to excel formulas, and while I have tried to solve this problem, I really need some help

I have a job sheet, with a job start date and a job due date. I manually create a timeline with 2 colors of cell to visually show when a job starts and when it is due.

Here's a screen grab:

So, I'd like to write two conditional formatting formulas, one that looks at the start date in column B, then colors the boxes on the right (in the visual timeline) blue from the start date up until the due date.

And a second formula that colors the due date red.

Many thanks in advance
Soozy

 

[Osvaldo Palmeiro]

Hello, Soozy.
Try this way and see if it could help.

Assuming the timeline starts on row 2, select that range starting in CS2 and extend it as desired.
In the Conditional Format >> New rule >> Use a formula... >> into the box formula put:
=AND(F$1>=$B2;F$1<$C2) and format to blue

In the Conditional Format >> New rule >> Use a formula... >> into the box formula put:
=F$1=$C2 and format to red

 

[soozy]

Hi Osvaldo

Thank you so much for you help. I now have an excel spreadsheet that functions the way I need it too . A colleague popped over to change the ; to a , in the formula, might be a mac/pc formula difference.

Many thanks
Soozy