Conditional Formatting (Table) Based on Dates

[psuedodragon]

Okay, so I've been trying to find an answer to this particular problem for a couple days now and no luck. There are instructions on conditional formatting applied to table, but I can't seem to tweak them so that they work for my specific situation.

Hopefully somebody here can help

I have a table that is meant to track the intake of new clients, and I'd like to have the text colour change based on one of two date entries. If there is an entry under "Closed" I want the text (of the entire row) to grey, whereas if the "Opened" date is more than 4 months ago I want the text to turn red.

I haven't even started trying to get that second bit working as I can't even get the text to grey.

(Here's a pic of the table I'm working with)

Please let me know if you need me to clarify what/how I'm using the table.

Thank you in advance!

 

[psuedodragon]

Changing it back to a "Short Date" format doesn't seem to change whether the formula works or not. Is there something else that could be interfering?

 

[mhillmann]

Yes, the reason is because the way you have it now is recognized as a String by excel. So it will not be able to format it or read it as a date.

Make a new column, paste this formula in it and drag it down to all the cells. It will (hopefully) give you the date in a format that you can use.

=DATE("20"&RIGHT(D5, 2), IF(LEFT(D5,3) = "Jan", 1, IF(LEFT(D5,3) = "Feb", 2, IF(LEFT(D5,3) = "Mar", 3, IF(LEFT(D5,3) = "Apr", 4, IF(LEFT(D5,3) = "May", 5, IF(LEFT(D5,3) = "Jun", 6, IF(LEFT(D5,3) = "Jul", 7, IF(LEFT(D5,3) = "Aug", 8, IF(LEFT(D5,3) = "Sep", 9, IF(LEFT(D5,3) = "Oct", 10, IF(LEFT(D5,3) = "Nov", 11, 12))))))))))), RIGHT(LEFT(D5, 6), 2))

 

[psuedodragon]

So, like I mentioned yesterday, I got fed up with it and left it for the night. When I came back into work today to try and opened the worksheet it... just randomly worked. So obviously your original formula was good. I just don't know why it wasn't working yesterday.

Thanks again!

 

[mhillmann]

Glad to help!