Conditional Formatting Help

[lynzwales]

Hello everyone, I hope everyone is well!

please bear with me I dont have the greatest technical knowledge of Excel so you're probably gonna really have to dumb any advice down for me haha!

I'm looking for a conditional formatting formula that will change a cell's colour after a certain amount of time. here's a scenario that will hopefully explain what i'm looking for:

I work in a complaints department where complaints have to be resolved within a week.

I log the complaint today on excel and it will be filled in Green. it will stay green for 2 days. When the complaint is Between 3 and 5 days old i want the cell to turn yellow and then from 6 days old onwards i want the cell to turn red because i really need to get the complaint resolved

the next day i log another complaint and want the same cycle of changes.

So far I've managed to find a formula which does the changes but can anyone confirm if it will work for my scenario?

Capture.PNG

when i entered another date, for example, 07/11/2018, to test it the box went yellow which suggests to me that the formula wants different dates to be entered to change colour. the date of the entry isnt going to change, just the age of the entry...

i'm hoping this is just because it wasnt the 07/11/2018 when i typed it though...

i hope this makes sense and someone can help me

thanks
x

[mehmetcik]

I would say No.

If the date in Column A is Not more than two days before today then turn green.
=$A1+2>=TODAY()


If the date in column A is not more than 5 days before today then turn yellow
=$A1+5>=TODAY()

If the date in column A is more than 5 days before today then turn Red
=AND($A1<>"",TODAY()-$A1>5)

[lynzwales]

Thanks for your reply

I'm not sure I follow I'm afraid

The date in column A will be "Today". the date per entry wont change but when the entry is 2 days old i want the cell colour to change from green to yellow then after 5 days old change from yellow to red

Thanks in advance

[mehmetcik]

When you put the date in column A it will go in as =today()

If you do not use copy paste value to fix that date then your data will be useless. You dates will always show todays date.

You should use a simple macro to enter the date in column A as soon as you click on a cell in column A. The Macro would then make sure it is not a formula.


Attach a sample workbook (not a picture or pasted copy). 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.

[mehmetcik]

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

[lynzwales]

Thanks for your help with this, i really appreciate it.

i'll certainly look in to the Macro thing, that sounds useful

I've attached the sheet i've been working on (theres no data on it except for todays date)

[mehmetcik]

Look at the file on my previous post.

If you click immediately below the last entry in column A, the Macro inserts the date.

Manually change the date to see the background colour change.