Conditional formatting with dates and colours

Occasional Contributor


I hope anyone can help, I would like to create a spreadsheet that uses colours to indicate if something is in date, is due or overdue.


For instance, if I see a client on one day and don't need to see them for 4, 6 or 12 weeks how can I display this using red amber green automatically?


I am ok with basic formula like =sum(c2+84) but much more then this I struggle and cant find out how to do this, I have been trying to do this for weeks messing about with conditional formatting but don't understand what the $ means in excel.


I have attached an example of what I mean.


Kind Regards



2 Replies



I have attached your sample sheet with the conditional formatting set up, I think it does what you are after. You can only conditional format on the value in the cell you are working on, not refer to other cells. The $ symbol is used to make a cell reference "Absolute" ie you can copy the reference to location and it will still point to the same cell reference as apposed to being relative where the cell reference will change when you copy it. 


Enter a value in cell A1, type =A1 in cell B1 then drag B1 to B2 and the formula will be =A2. Put a $ in =A$1 and drag it down and the formula will be A1 because you made the row reference (1) absolute.


Hope this all helps,



Thank you very much rich, I will check this out after and get back.


Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies