RAG rating for dates in different cells

[Richie872]

Hi,
I hoping someone can help me as I tried various formulas and conditional formatting.

I have a spreadsheet for work that I want my staff to use but I want from the contact column (E3) when the staff enter the date of contact the cells in column I (I3) to populate and change colour based on day range.

For example
if E3 has a date of 10/08/2022 then cell I3 initially populates for 31 days (a month) but during these days up to 15 days green, 16-30 amber and 31 + red so it's a tracker for staff to review their clients and make contact. So everytime the date in E3 is changed the other cells continue to match

I have added my spreadsheet for ease.

It maybe me over complicating things I'm not sure

[Estevaoba]

Hi, Richie872. Welcome to the forum.

This formula to populate column I with dates past one month from column E:
=IF(E3="","",EDATE(E3,1))

Then conditional format with formulas, for example this for green:
=TODAY()<=$E3+15

Please check wb attached.

Good luck!

[Richie872]

Hi Estevaoba,

Thank you very much for helping. I don't know if it's me but the colour doesn't change from green to amber or red when the dates are changed. Do you know why that may be? or do i need to do something

[Richie872]

Hi Estevaoba,

Thank you very much for helping. I don't know if it's me but the colour doesn't change from green to amber or red when the dates are changed. Do you know why that may be? or do i need to do something

[Estevaoba]

I may have misread your goal.

The conditional format rule will change the color to amber when current date (Today) is 16 up to 30 days past the date in column E.
And it will change to red when one month or more has passed since that date.
When you changed dates, did you simulate that?

In the new wb attached, in the conditional format rules I replaced the function TODAY() with $I$1, which simulates the current date.
You can change the value in H1, this value will be added to Today's date in G1, so the date in I1 will reflect that change.

If result is not what you expect, please clarify.

[Richie872]

Hi Estevaoba,

Thank you for your patience.

I think that is correct.

Basically when a staff member manually enters a date into column E and not just today's date so therefore column I populates as you have done.

So say I contacted you today and then inputted today's date in column E I have 30 days to review and contact you again before going red in column I
If I contacted you say last week but forgot to input the date so say 9th Aug again column E would say 09/08/2022 but column I would still recognise it's not today's date and populate column I with the correct date and colour.

Again thank you for your help

[Estevaoba]

You're welcome.
Thank you for the feedback and for the reputation added.
Have a blessed day!

[Richie872]

I tested it today with a colleague and inputted data but colours didn't change to green when within the 15 day window prior to going amber.

[Estevaoba]

Maybe if you would upload a new sample wb explaining what happens versus what you expect.

[Richie872]

Hi,

Thank you for your patience it would be a lot easy if there was a zoom or teams function lol. I think I have explained it better in the attachment.

[Estevaoba]

Hi.

The conditional format in the wb in post #2 works just as requested in your last sample wb.

It has been setup with formulas for all three cases:

=TODAY() < =$E3+15 for green

=AND(TODAY() > $E3+15,TODAY() < $I3) for amber

=TODAY() > =$I3

[Richie872]

All sorted apologies for the delayed reply. Thank you again for your help