Excel 2007 - Conditional formatting- comparing dates 1

[doodler22]

I'm trying to set up conditional formatting to show buttons (dots) comparing dates.

Let's say I have a target goal date - 10/9/2009. The actual date of the occurence was 10/12/2009 (so we're off by 3 days.) I want to set up conditional formatting so that anything below or at the goal date comes up with a green button, anything 1-3 days after the goal date shows with a yellow button, and anything 4+ days shows with a red button.
How do I do this?

 

[SkipVought]

Hi,

The only conditional format feature in Excel is related to CELLS.


Select the cell(s) you want to CF and Format > Conditional Formatting... Formula Is

then select in adjacent textbox and select the the cell contaning the date value and and compare to TODAY()+WHATEVER like...
[tt]
=AND($d$5>=TODAY(),$d$5<=TODAY()+4)
[/tt]
and then set a pattern format.

The cell will get the pattern shading when D5 meets the criteria.

Anything more complex or related to other objects, post your question in forum707 for VBA code help.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

In this example, I'm using a Named Range, rngGoal, which refers to the cell that contains the Goal date. If you put the goal in, say, A1, then you can select cell A1 and click in the little box above it - to the left of the formula bar - and type in rngGoal.

[ul][li]Select the column that has the dates you want to compare[/li]
[li]Go to Home > Styles > Conditional Formatting > New Rule[/li]
[li]Leave the top selection on Format all cells based on their values[/li]
[li]Set "Format Style" to Icon Sets[/li]
[li]Set "Icon Style" to 3 Traffic Lights (Unrimmed) (or whatever set you want to use)[/li]
[li]Check the box beside Reverse Icon Order[/li]
[li]Change the Red traffic light to >= ; =rngGoal+4 ; Number[/li]
[li]Change the Yellow traffic light to > ; =rngGoal ; Number[/li]
[li]Notice that the Green traffic light sets itself based on what you enter for the first two[/li]
[li]Press OK[/li]
[li]Press OK[/li][/ul]

NOTES:
- You'll need to change the "Type" before entering the "Value"
- You'll need to put the "=" in front of the Named Range in the "Value"

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Skip: Conditional Formatting got a pretty major overhaul in 2007. You no longer have to look out for blank cells below your table (assuming your data is in tabular format) and you no longer have to define each criteria separately.

As with many things in Office 2007 it's very different, but easier once you get used to it.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

ah yes, it is 2007 in the TITLE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[doodler22]

Thanks...you're directions were very good, although I don't think I explained it quite right. Try this:

Column A Column B Column C Column D Column E Col F
Task Timeframe Budgt Start Budgt End Real Start RealEnd
RFA 7 days 10/15/08 10/22/08 10/16/08 10/29/08

I'd like to compare the date in Column C's cell to the date in column E's cell. It's off by 1 day, so I'd like to have a green dot to show in Column E's cell to show that this date (Column C's cell) was met. I'd also like to have a red dot to show in Column F's cell to have a red dot to show that the goal date (Column D's cell) wasn't met.

I have about 20 rows (tasks). It stretches an entire year.
So how to I compare Column C (cell 3) to Column E (cell 3) and then Column C (cell 4) to Column E (cell 4), etc. And then Column D (cell 3) to Column F (cell 3), etc?? Where do I put the rngGoal?

 

[doodler22]

I got it! Thank you SOOo much John (anotherhiggins). I think I didn't think it was working earlier because so many of my dots were red, but that just means that we are sooo off our targets. This will be a huge help.