Conditional Formatting Issue

[jojo750]

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

[#2 · Accepted solution]

@jojo750 

Please see attached workbook.

[#3]

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.

[#4]

You're welcome! Enjoy the weekend.

[#5]

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

FREQUENCY   3

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.

[#6]

@jojo750 

=EDATE(H3,I3)

[#7]

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

[#8]

@jojo750 

Have you tried it?

[#9]

@jojo750 

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

[#10]

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

[#11]

@Hans Vogelaar 

Yes,  thank you for your help.

Jo