Conditional formatting based on past and future dates

[Helen842000]

Hi all,

I'm struggling to know the right order to put the conditions in, if anyone could help please.

I would like empty cells to stay blank/white
Any cells with dates in the past or in the next two weeks (i.e past due or due soon) to turn RED
Any dates not due in the next two weeks can stay blank/white.

I've tried every combination of TODAY()+14 or -14 but all I end up with is a red column of blank cells grrr!

Any help would be greatfully appreciated.

Thanks!

 

[Joe4]

Any cells with dates in the past or in the next two weeks (i.e past due or due soon) to turn RED

Let's say it is cell E1 you are checking. This is the formula I would use:

=AND(E1 < TODAY()+14, E1 > 0)

 

[Helen842000]

Thanks!

How would I do that within the CF box? Would I use Cell Value is or Formula IS? I'm doing it to a whole column
Cheers!

 

[Joe4]

Since it contains a cell reference to the cell you want to format, you will use the "Formula Is" option.
If you highlight the whole range you wish to apply it to, then go to Conditional Formatting and write the formula as is you were only applying it to the first cell of your highlighted range, it will automatically adjust your formula for the rest of your range.

 

[Helen842000]

Great, thank you very much! That works perfectly!

Easy when you know how eh!, appreciate it - cheers!