conditional formatting - changing a cell colour depending on how late a date is.

[Lynsey]

Hi, Hope someone can help.

I have a spreadsheet (excel 2013) and I need the date reviewed column to change color depending on how late a report is. So I need a formula within conditional formatting that will do the following:

• if date entered is later than todays date by 6 months change to blue
• if date entered is more than 6 months old change to red

On my workbook the cell reference is P2
Have looked everywhere for the answer and just can't seem to get it!

Thanks if you can help!

 

[NARAYANK991]

Hi Lynsey ,

Can you clear a doubt or two ?

1. You have a reference date in P2 ; all dates in the date reviewed column need to be checked against this date ; is this correct ?

2. The date reviewed column will already have dates entered in its cells ; correct ?

3. To format a cell which contains a date , the formula will check if that date is greater than the date in P2 ? Or is it check if that date is less than the date in P2 ?

All dates in the future will be greater than today's date.

Please clarify whether the rule should be :

Cell date - P2 <= 6 months

or

P2 - Cell date <= 6 months

Secondly , does 6 months mean we check for the date entered in P2 + / - 6 months using the EDATE function , as in EDATE(P2,6) or EDATE(P2,-6)

Narayan

 

[Lynsey]

Hi Lynsey ,

Can you clear a doubt or two ?

1. You have a reference date in P2 ; all dates in the date reviewed column need to be checked against this date ; is this correct ?

2. The date reviewed column will already have dates entered in its cells ; correct ?

3. To format a cell which contains a date , the formula will check if that date is greater than the date in P2 ? Or is it check if that date is less than the date in P2 ?

All dates in the future will be greater than today's date.

Please clarify whether the rule should be :

Cell date - P2 <= 6 months

or

P2 - Cell date <= 6 months

Secondly , does 6 months mean we check for the date entered in P2 + / - 6 months using the EDATE function , as in EDATE(P2,6) or EDATE(P2,-6)

Narayan

Hi Narayan,

in Column P I have dates which are automatically calculated from dates which are entered into Column O.

I need the cells in column P to change colour depending on the date that is automatically added into it.

So I need the cells to change colour if:

turn Blue if the date is between today and 6 months late

and turn red if the date is over 6 months late

Hope this clears it up.

Thanks,

Lynsey

 

[NARAYANK991]

Hi Lynsey ,

Let me try to understand this ; column P has dates , let us say in a range from P2 through P100.

Cells in this range will either be blank , or will have dates in them. Can you say whether these dates will be in the past or can they also be in the future ?

Secondly , I am not able to understand the logic for colouring a date BLUE or RED ; today is June 20 , 2014.

Suppose we have the following dates in column P :

June 20 , 2013

March 20 , 2014

September 20 , 2014

June 20 , 2015

What will each of these dates be coloured ?

Narayan

 

[Lynsey]

The dates in column P will be either in the past or in the future or blank, as some reviews have been completed ages ago and some reports may have only just been completed and will not need to be reviewed for a while.

June 20th 2013 would be RED as it's over 6 months old from today and March 20th 2014 will be blue because it less than 6 months late.

We need to identify who is late and who is really late with their reviews! Hence the two colors.

 

[NARAYANK991]

Hi Lynsey ,

See if this is OK.

Narayan

 

[Lynsey]

Yeah that's great, thank you