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
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
