Conditional format formula using =today() and greater than / less than

0
votes

Trying to set up a bit of a to do list with conditional formatting dates. Any time I open the file B2 will load =today()'s date. What I'm hoping to do is find a formula that will allow something like if C2 < B2 (Over due) then conditional formatting rule, or if C2 is 7 days more than B2 then format rule 2.

Not sure how to proceed with this as =today() isn't being treated like numeric data.

Any suggestions?

Thanks!

1
excelconditional-formatting
Remember that if C2>B2 then C2 is in the future. – Gary's Student
Official help on conditional formatting already use today(), have you tried that? support.office.com/en-au/article/… – Máté Juhász
Whoops good catch - thanks I'll edit that. Still not sure where to go beyond that =if($C$2>$B$2) to apply the conditional format. I know traditionally the true/false statements would come next, but what in a format? – BR89
@Máté Juhász. I had not seen that resource. Thanks! This should help steer me and fix the over complication – BR89
No idea what the heck I was using wrong initially but after wiping the rules and reapplying using the tips in that posted guide - it's up and running, Thanks! – BR89

1 Answers

0
votes

To apply to C2, select it and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=C2<B2

Format..., select choice of formatting, OK, OK.

Then repeat with: 

=C2=B2+7

and (presumably) a different choice of formatting, though could be the same (in which case a single rule would be sufficient).

Beware if C2 contains time as well as date the second rule above will not work (and not that formatting will only work for 7 days time, not 6 nor 8 ahead etc, only seventh day from today not counting today).

TODAY() could be part of the formulae (if adjusted) - does not need to bein a separate cell.

To apply to ore than one cell (eg an entire column) is quite possible with slight adjustments.