Conditional formatting using a formula that calculates difference between current date and date in another cell

[smsolomon]

Just started with Excel 2013.

Creating a tracker in which a suspense date is in one cell and then based on the current date the 'status' will change color (4-6 different colors). Example: Today is the 28th of May 2015 and the suspense date shown in cell A1 is 1 June 15. So it is currently 5 days from the suspense date. I want the color in the status cell (A5) to be RED for any dates that are within 5 days to the suspense date. If the difference is between 6 and 14 days, then the color changes to YELLOW....if the date is past the suspense date then the color is BLACK. Basically the color changes depending upon the difference between the current date and a specific number of days (0 to 5 days, 6 to 14 days, 15 to 21 days, etc). The status' cell color will change as the days go ..... any help is appreciated.

 

[AOB]

Hi smsolomon,

You could use variations of the following formula for each condition :

Format black (suspense date is prior to today's date)

=IF($A1 (is less than) TODAY(),DATEDIF($A1,TODAY(),"d")*-1,DATEDIF(TODAY(),$A1,"d")) (is less than) 0

<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0[ QUOTE]
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0[ CODE]

Format red (suspense date is within 5 days of today's date)

=IF($A1 (is less than) TODAY(),DATEDIF($A1,TODAY(),"d")*-1,DATEDIF(TODAY(),$A1,"d")) (is less than or equal to) 5

<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5

Tick all as "Stop If True" and prioritise in ascending order (so <0 first priority, <=5 second)
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5[ QUOTE]
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5
<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5[ CODE]

etc. etc.</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5[></today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5


** Message editor keeps </today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5[></today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5
cutting the formula with a "<" symbol so replace (is less than) above with the "<" symbol, (is less than or equal to) with "<=" **</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5
</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<=5
</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0[></today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0<today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0[></today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0
</today(),datedif($a1,today(),"d")*-1,datedif(today(),$a1,"d"))<0

 

[smsolomon]

When I changed the formula to show D3 as the cell which has the suspense date and changed the less than and equal to or less than as you stated. The outcome was the word FALSE in the status cell.

So how do I show colors and also how do I combine them so that depending upon the date it could show a different color. Here is the formula:

=IF($D3 <TODAY(),DATEDIF($D3,TODAY(),"d")*-1,DATEDIF(TODAY(),$D3,"d"))<= 5<today(),datedif($d3,today(),"d")*-1,datedif(today(),$d3,"d"))<= html="" 5<=""></today(),datedif($d3,today(),"d")*-1,datedif(today(),$d3,"d"))<=>

 

[smsolomon]

=IF($D3 <TODAY(),DATEDIF($D3,TODAY(),"d")*-1,DATEDIF(TODAY(),$D3,"d"))<= 5

 

[AOB]

Where are you putting the formula?...

 

[AOB]

Select the range you want the conditional formatting applied to (i.e. column D, presumably?)

Then...

Home > Conditional Formatting > New Rule...

Select a Rule Type : "Use a formula to determine which cells to format"

Under "Format values where this formula is true:" add your formula

(As per your previous post, you can see that the formula will either return a TRUE or FALSE value - in situations where it returns TRUE, the formatting will be applied, where FALSE it will move onto the next rule applicable to that range)

Use the Format... button to specify the format to be applied to the cells which meet the criteria (Number, Font, Border & Fill)

Hit OK

Repeat for each scenario you want