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

#### smsolomon

##### New Member
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Hi smsolomon,

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

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

Code:
``=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)

Code:
``=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

Last edited:
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"))<=>

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

Where are you putting the formula?...

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"

(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

Replies
0
Views
464
Replies
3
Views
664
Replies
3
Views
348
Replies
1
Views
1K
Replies
9
Views
466

### Forum statistics

1,202,984
Messages
6,052,914
Members
444,612
Latest member
FajnaAli ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back