Conditional Formatting NOT BASED ON TODAYS DATE.

Hi guys I have searched the internet far and wide but all forums just give me conditional formatting based on the "TODAY" function. I would like to have a cell turn "Green, Yellow and RED" based on a specific date that I have placed next to it. Any help please... I have attached a mock-up of what I am looking for. This is an outcry to all you intellectuals out there

So the time in terms of days or fractional days (the way Excel measures time) will control the fill color? You can subtract one date from another (they are values) and based on that result, build a few conditional formatting rules to generate the desired fill color. You could also use color scales that will provide you the opportunity to determine the break points and the number ranges that generate one color or another.

HansensOffice.com

OK I have used the "average function" so that gives me a value in "cell F2" smack in the middle. By using the Format all cells based on their value and selecting 3-color scale as my format style am I on the right path to figure this one out???

Originally Posted by sovero

Hi guys I have searched the internet far and wide but all forums just give me conditional formatting based on the "TODAY" function. I would like to have a cell turn "Green, Yellow and RED" based on a specific date that I have placed next to it. Any help please... I have attached a mock-up of what I am looking for. This is an outcry to all you intellectuals out there

When applying your conditional formatting use - "Use a formula to determine which cells to format"

For example

Please Login or Register  to view this content.

This does a check whether the number of days between E2 and D2 is greater than 5, if true, then it colors the cell red

You'd just need to create two more entries for conditions that you want cells to be colored green and orange

I see what you mean ThirtyTwo. Generally the equipment gets services every two weeks. But there has to be something keeping count of days passing. So if it is only due in two weeks time then it is still green. If it is due in a weeks time yellow and 5 days before services due red. Hope that makes sense...??>>>>

OK I have figured out some of it... What I used is the "DATEIF" or let me say the new xlDATEDIF. So the formula looks like this =xlDATEDIF(TODAY(),E2,"D").

The problem is this...

Excel show 6 days in cell "F2" but the actual value behind the scenes are 38718.00... Which make conditional formatting useless

Any suggestions..???

I will upload the doc so you guys can see what I am talking about...

Hi -

Subtract the date in column D from the date in column E and you will get the days elapsed between the two dates. There is also a way to get the working days between two dates while defining holidays if your interested.

After getting your result you can add some conditional formatting rules or color scales.



HansensOffice.com