RAG rating, Formulas and Conditional Formatting. HELP!!

[DebsV]

Hi. Ok, i'm trying to conditional format a column to RAG rate when i'm approaching deadlines for a specific date. The dates i want my formula to look at are already in my table, but i don't know how to write the formula.
Here's what i want column I to say -

If today is greater than or equal to the date in F2 and is less than or equal to the date in H2, turn green.

If today is equal to H2+1 but less than or equal to I2, turn amber.

If today is greater than I2, turn red.

God I hope that makes sense to somebody.

Thanks in advance!

[TMS]

Suggest you upload a sample workbook with some typical examples. Makes it much easier to envisage and formulate a working solution.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FlameRetired]

Hi DebsV and welcome to the forum.

but i don't know how to write the formula

It sounds like you will need 3 formulas.

But like TMS says it will be simpler if you upload an Excel workbook sample.

[DebsV]

Thanks guys. So I seem to be having some issues uploading my example file at the moment which is quite frustrating. I'll keep trying, however if i can't, is there any other way i could better show what i mean?
Thanks.

[TMS]

If today is greater than or equal to the date in F2 and is less than or equal to the date in H2, turn green

Please Login or Register  to view this content.

format green

If today is equal to H2+1 but less than or equal to I2, turn amber.

Please Login or Register  to view this content.

format amber

If today is greater than I2, turn red.

Please Login or Register  to view this content.

format red

You might need to AND extra conditions if any of the cells can be blank so that you are only comparing dates.

Anyway, something to play with.

[DebsV]

Ah thanks so much! Very nearly there! However, the amber formula doesn't seem to be working? For example, i would expect "I" below to be amber, as today's date is between 11/10/16 (H2+1) and 24/10/16. The green and red formulas work fantastic, so thank you very much for that. It's been bugging me for ages.

F H I
26/09/2016 10/10/2016 24/10/2016

[DebsV]

Sorry, F, H and I were above each date before i posted.