SOLVED

Conditional Formatting Issue

Copper 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)
Solution

@jojo750 

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

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.

@jojo750 

=EDATE(H3,I3)

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

@jojo750 

Have you tried it?

@jojo750 

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.

Jo

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@jojo750 

Please see attached workbook.

View solution in original post