SOLVED

Conditional Formatting using =TODAY formula

Copper Contributor

Hello everyone,

 

I’m trying to create some formatting rules in Excel where the formatting rules include the =TODAY formula.

 

I’ve managed to reach the desired outcome for a single row (the formatted cell will appear red, orange, or green depending on how many days are left until today), but the problem is that the dates on each row change but the conditional formatting rules are static.

 

Is there a way for me to apply the conditional formatting formula dynamically so that it adjusts for the values in each row rather than being attached to the data in one specific cell?

 

Example:

 

Column A is the date a file was assigned. A1 is 15/04/2024

Column B is the date by which we need to have responded to that assignment. B1 is =A1+14 (we have 14 days to respond)

 

Conditional formatting is as follows:

 

First rule - =A1<=TODAY()-10 - if criterion applies, cell B1 will turn red

Second rule - =AND(A1<=TODAY()-5) - if criterion applies, cell B1 will turn orange

Third rule - =A1>TODAY()-5 - if criterion applies, cell B1 will turn green

 

This is great for knowing whether cell B1 is within those thresholds. But when I enter a new date into A2, the formatting in cell B2 is judged against the data inputs from A1. I need the formula to replicate across each row so that each date in column A is judged accordingly.

 

Hopefully that makes sense, and if anyone can provide any assistance at all then it would be greatly appreciated!

2 Replies
best response confirmed by BJDA88 (Copper Contributor)
Solution

@BJDA88 

Does the attached example show what you want to do?

 

=$A1<=TODAY()-10

This is the first rule for conditional formatting. I've only added the $ sign to your rule.

=$B$1:$B$9

This is the range the conditional format applies to in the example.

Hi Oliver,

This seems to do exactly what I need - thank you so much for your help! Appreciate you may not have time to but, if you do, can you explain why my formula didn’t work in this same way?
1 best response

Accepted Solutions
best response confirmed by BJDA88 (Copper Contributor)
Solution

@BJDA88 

Does the attached example show what you want to do?

 

=$A1<=TODAY()-10

This is the first rule for conditional formatting. I've only added the $ sign to your rule.

=$B$1:$B$9

This is the range the conditional format applies to in the example.

View solution in original post