Conditional Formatting (Table) Based on Dates

psuedodragon

New Member
Joined
Dec 5, 2013
Messages
22
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)
Excel%20Intake%20Chart_zpssryy8g01.jpg


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

Thank you in advance!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Got it. Use these as formulas in the conditional formatting panel.

Turn this one red:
Code:
=DATEVALUE(TEXT($D3, "mm/dd/yyyy")) + 30 < DATEVALUE(TEXT(TODAY(), "mm/dd/yyyy"))

Turn this one grey:
Code:
=NOT(ISBLANK($K3))

Then make sure you have it apply to all the cells in the table, not just rows D and K.
 
Upvote 0
Sorry about the double post, but I thought I might make a mock-up of what I (visually) want the table to look like when working. Now, I added extra shades of text based on just how far back the "Opened" date was, but I'm not sure if I'll actually need that for the table. (And that's a pretty easy tweak once we've figured out the base formula needed)

Excel%20Intake%20Sample_zpsoqunwdcl.jpg


If it matters -the data is either going to be kept ordered based on the date "Opened" or alphabetically by name. (So ideally, some sort of formula that doesn't break if I switch between those two sorting options)
 
Upvote 0
I forgot to mention a few things. The + 30 means 30 days between today and the date shown. You can change that if you wish. Also you need to order them correctly, the one that turns grey being higher on the list, so that the red ones turn grey once a finished date is added.
 
Upvote 0
If you are using a custom format, then no. If not, there may be problems. Let me know how it goes.
 
Upvote 0
:S The grey formula works great, but I can't get the other one to change colour. I'm using a test date "Opened" almost a year ago and it's still stubbornly black.

Originally I'd just typed it into the formula box (so I could adjust it from 30 to 120 days) and when it didn't work, I actually just copy and pasted the formula you shared above. Still no change.

I feel like I'm missing something really small and obvious that's messing this up >.<
 
Upvote 0
It is because the formula works under the assumption that excel knows which date you are talking about, but the format you are using is not recognized... I wrote this, it is long but it should work for you.

Code:
[COLOR=#333333]=DATEVALUE([/COLOR]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))[COLOR=#333333]) + 30 < DATEVALUE(TEXT(TODAY(), "mm/dd/yyyy"))[/COLOR]
 
Upvote 0
First of all, I really really (really) appreciate all your help!

But it still isn't working. :S Might have to take another run at it tomorrow (as my work day is almost done, and this is giving me a headache so I won't work on it at home).


Thinking about it, Excel should be able to recognize that it's a date. I enter the date as 05/04/2014 (for example) and I just have it set up so that it shows up in the cell as Apr 05/14... but I could be wrong.
 
Upvote 0
You are actually not wrong. That is possible to do with Excel, it just won't let you do it in reverse. It has to do with the way dates are actually stored in the program. Starting with 1/1/1900 they are stored as an integer in number of days.

Without being too wordy: You're right but excel doesn't like that.

The best way to go from here would be to format the cells so that they read as a normal date would (mm/dd/yyyy) or some version of that.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top