Conditional formatting: various conditions using two date columns incl when cell = blank

Hi,

I have searched for problems like mine and found some useful tips, but nothing which quite gives me what I need. I hope someone can help and enjoy the challenge set here...

I have 2 columns referencing dates and I want the text in each to change colour according to a number of varying cases:

Column L is the Planned order date
Column M is the Date ordered


Here are the conditions: I am only having problems with no 1)

1) Planned Order date (Column L) to show orange text when the date in it IS today AND the date ordered (Column M) is blank (i.e. highlight items that need to be ordered today)

2) Planned Order date (Column L) to show red text when the date in it is BEFORE today AND the date ordered (Column M) is blank (i.e. highlight items that are now overdue to be ordered)

3) Date ordered (Column M) to be red text when the date in it is AFTER the planned order date (Column L). (Item has been ordered, but was ordered later than planned)

4) Date ordered (Column M) to be green text when the date in it is BEFORE the planned order date (Column L). (Item has been ordered, and was ordered earlier than planned)


I am familiar with setting conditional formatting and have been trying a variety of formulae, but it is not quite behaving as I expect. I'm wondering if the order of the rules matters ?

I'm also not sure when to use absolute references ($), as this conditional formatting is needed in repeated cluster of cells (e.g. L83:L93 & M83:M93, then again at L125:L135 & M125:135 and so on down to L12739:L12749 & M12739:M12749). This is because the spreadsheet is set out as a kind of form with specific cells relating to specific things within a project.


So far I have:

For Case 1) above
=AND(L83=TODAY(), M83="") - turn text in L orange

For Case 2) above
=AND(L83<TODAY(), M83="") - turn text in L red


The result is that Case 2) works properly and the text in Column L goes red if the date entered there is before today and Column M cell is blank, but Case 1 where the date in Column L is today's date and the corresponding cell in Column M is blank, the text in column L stays black and doesn't go orange. This is the result whichever order I put the CF rules in.

I can confirm that the date I have put in is the same as showing on my computer and that the CF rule is active on the cells I am testing.


Cases 3) & 4) seems to be working OK:

Case 3)
=M9155>L9155 - turns text in M red


Case 4)
=M9155<L9155 - turns text in M green



Your help would be appreciated.

Many thanks,

Fiona