Who likes a challenge? - excel formula's 4

[wakubi]

Hi everyone,

I work in IT Support an someones just sent me this!
I havent a clue myself.....



If someone who uses excel is feeling charitable and has a bit of time to kill please can you take a look at this...

A user has an excel spreadshee with a list introduction dates.
what the user wants to do (if possible) is to set up a formula like a timer so that if the date is older than 30 days then the cell turns green, when it hits 60 days, amber and 90, red. really just some sort of visual indicator rather than trawling through shed loads of numbers.

It doesnt sound too complicated but I've been trying to work it out for the last hour or so to no avail and I thought I would just try youselves.

Like I said if you can then great if you're not interested please pass it back

Cheers

 

[bluedragon2]

conditional formatting

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Format / Conditional Formatting

1st Cond
Cell Value 'is equal' to 'less than' =TODAY()-90
Click format button and set pattern to red
Click Add

2nt Cond
Cell Value 'is equal' to 'less than' =TODAY()-60
Click format button and set pattern to amber
Click Add

3rd Cond
Cell Value 'is equal' to 'less than' =TODAY()-30
Click format button and set pattern to green

3 is max number of formats, unless you include default format as the 4th. More than that needs VBA.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[wakubi]

wow, thanks guys

 

[CandyS]

(...I can't believe I'm actually providing help instead of asking for it!)

This should seem simple, but to us non-wizards it can be frustrating (I'm an apprentice witch, by the way, thanks to the wizards' help!).

I've done this a bunch of ways--some very involved to consider weekends and holidays into the calculation (I can provide them if requested). However, it seems you're probably working off of "fixed" calendar dates, that is you need to measure aging against either 'date started' or 'target date of completion' dates.

In any case, this is how I have mine set up:

A B
START DATE AGING
2 12/01/03 =(A2-(TODAY()))*-1 returns "43"
3 12/15/03 =(A3-(TODAY()))*-1 returns "29"

Conditional Formatting: Highlight the range in column B you wish to format, go to Format->Conditional Format, change "Cell Value Is" to "Formula Is" for each of the three conditional formats:

Condition 1: Enter =B2<31, choose &quot;Format&quot; (I chose bold white font and green background (&quot;Patterns&quot; tab)

Condition 2: =IF(AND(B2>=31,B2<=60),TRUE,FALSE)
(I chose bold black font and gold background)

Condition 3: =B18>60
(I chose bold black font and red background)

Choose &quot;OK&quot;.

That should do it.

Candy

 

[CandyS]

Ken's a freakin' genius!

(re-working reports as we speak...)

Thanks.

Candy

 

[KenWright]

LOL - Just don't tell Geoff (xlbo) that, as he will tell you to ask me about VLOOKUPs, and then I'll squirm

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Bowers74]

wakubi,

You need to realize, unfortunately, that the Conditional Formatting tool in Excel only allows you to set three different conditions. If you want to have more than three different criteria for the conditional formatting then you will have to use VBA code.

You might want to inform your user on this, just in case.

To everyone else, good job and to KenWright: Good Explanation!!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884