Conditional Formatting Issue

Occasional Contributor

Hi all,


First time post, I am trying to complete a conditional formatting task for the following;


Due date column (which is calculated from a previous cell) but want to colour code as follows;


Red - if date is today or before 

Yellow - if date is tomorrow and 30 days after 

Green - if date is 31 + days after


Can anyone help please.


Thanks  Jo

10 Replies
best response confirmed by Hans Vogelaar (MVP)


Please see attached workbook.

Thank you so much, it has worked and you have saved me from what would have a been a long winded task for to work it out.
You're welcome! Enjoy the weekend.

HI Patrick,
Can I pick you brain for one more thing...
I am trying to add a formula to calculate the next appointment date based on a previous date but have a frequency column in the middle to dictate time between;

ie; '3' - 3 months (90 days)
'4' - 4 months (120 days)
'6' - 6 months (180 days)

LAST SEEN     01/04/23


NEXT DATE    =H3+I3, IF(I3=3, (then +90), I3=4 (then +120), if i3=6 (then +180))

Think this is the last one, actually managed some of the others.



Thank you but I need the cell I3 to identify '3' as 90 days, '4' as 120 days and '6' as 180 days depending on frequency for next date .
Not sure if I have explained that very well, sorry


Have you tried it?


I'd go with @Hans Vogelaar 's solution for this one.  Your data is arranged perfectly for EDATE.

Hi Both,
Thank you, it works but I had complicated the frequency cell with a look up.

@Hans Vogelaar 

Yes,  thank you for your help.