Need urgent help with conditional formatting

[drey_d]

Im programming an Account Manager's Report in Excel. So here's the scenario,
I need to make a formula using conditional formatting on an excel document. And no macro or vb isnt an option. The user just wants it in plain conditional formatting formula...the fields or should i say columns are:

* project phase or milestone name(name of phase in the project)
* milestone deadline (when the milestone is expected to be accomplished)
* milestone status (status in project time which tells wether its progress is on time or late. Its classified as either Completed or Planned)

the rows are of course milestone phases...

THE OBJECTIVE:
Format the above described table with conditional formatting that will:

*Find the oldest milestone deadline date (which is in column B2 downwards..but for the formula lets pretend the rows are until B4)
It will start formatting the milestone with the earliest (oldest) deadline then moving to the next oldest.. until beyond the current date

* The formula will format each milestone (the whole row containing its details) like mentioned earlier by date order (from oldest)

So the conditional statement to be done for each milestone is:

1st statement ...or Rule, i dont know if you can just put all statements in one formula
If (Milestone Status (or C2 TO C4) is "Completed" ) OR (Milestone Deadline is = OR more than 30 days before current date )
if TRUE then highlight row color GREEN (ex. row A2 to C2)

of course if the above statement is false, the Milestone Status is none other than "Planned" so this it goes for the second conditional statement ....

2nd Statement:
first condition
If (Milestone Deadline = 15 or more days but less than 30 days before Current Date)
if TRUE then (highlight Row AMBER)
(if false - proceed to next condition )

second condtion
If (Milestone Deadline = or after Current date) OR (Milestone Deadline is = 10 days or less before current date)
if TRUE then (highlight Row RED)
(if false - NO ACTION ) though i dont think that any of the dates will reach this point!

What I want the output to be is that The Account Manager will easily be able to see the most urgent pending milestone that have to be finished (basing on the nearest to the current date). So its basically nested conditional formatting.. with just a little twist.. its a heck of a mind twister coz I'm not used to applying conditional statements to excel, i dont really know how to use the formulas and rules! This will also later be used in the database where the user extracts the actual information...

thanks in advance,
drey

 

[drey_d]

i thought so. hehe
i really dont know whats wrong, but now nothing happens even if the formula is active. Should I create a new excel document with similar values you gave me?

thanks a million M!​

 

[Marcelo Branco]

Hi M,

Excel wont accept the last formula you gave me, it flashes an error message, and when click apply on the first two,nothing happens..what do i do now

The first two formulas should work...and also the third - in english, of course.

Do you have real dates in B2:B6 or text?

M.

 

[drey_d]

I'm using dummy data at the moment, but yes I did put real date, for the month of October and September. Nothing has happened to the selected cells although i did clear all the rules in the Rules Manager...

 

[Marcelo Branco]

i thought so. hehe

i really dont know whats wrong, but now nothing happens even if the formula is active. Should I create a new excel document with similar values you gave me?​

thanks a million M!​

For testing purpose type in B2:B6 real dates.

If you use mm/dd/yyyy format type
10/30/2011
09/30/2011
09/12/2011
09/20/2011
09/17/2011

 

[drey_d]

Hi M,
ok, well the red has popped up, but still nothing on the Green and Amber (orange)

 

[Marcelo Branco]

Hi M,
ok, well the red has popped up, but still nothing on the Green and Amber (orange)

Just the last formula (the one i forgot to translate) is working ...

For me everything works fine (excel 2010)

By the way, what Excel-version are you using?

M.

 

[drey_d]

Hi M,

Im using excel 2007, the 3 color have popped up, but there are 2 rows which are left white? i shall check the dates, and make them the same as your example..

 

[Marcelo Branco]

Hi M,

Im using excel 2007, the 3 color have popped up, but there are 2 rows which are left white? i shall check the dates, and make them the same as your example..

wow!! We have 3 colors! Great!

Too late for me... 5am in Rio...going to bed

M.

 

[drey_d]

Hi M,

The 3 colors are all working, but theres a problem with the amber/orange. Only one of the rows turns to amber. The second row with the date 09/30/2011. On one of the dates I used 09/20/2011, which falls under the orange category.but nothing happens to its row, when i try changing the formula, c2 to c6, the 09/20/2011 becomes white too..

 

[drey_d]

Ok,
im sorry i think i made a mistake the date on b3 is = 09/30/2011 = Planned but b5 is
09/20/2011, has status: completed, so when i turn 09/20/2011 status to planned, it turns red, but if completed, turns white, shouldnt it apply to 1st statement, (GREEN)