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]

So ive put the formula for amber back to the one you posted, because i think it should either fall under red or green.

green because it applies to the completed as true
red if the status goes back to planned, falls under red???

 

[Marcelo Branco]

Hi drey_d


If you change the Status in C5 (B5=20/09/2011 in dd/mm/yyyy) to Completed you get Green according to your 1st rule

<TABLE style="WIDTH: 166pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=221><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=70>Milestone</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>Deadline</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=76>Status</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=20>PHASE1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64 align=right>30/10/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63>Planned</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #c9a6e4; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=20>PHASE2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #c9a6e4; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64 align=right>30/09/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #c9a6e4; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63>Planned</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=20>PHASE3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64 align=right>12/09/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63>Planned</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=20>PHASE4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64 align=right>20/09/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63>Completed</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=20>PHASE5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64 align=right>17/09/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63>Completed</TD></TR></TBODY></TABLE>

Back to Planned it turns Red accordingly to your last rule.

Isnt this ok?

M.

 

[drey_d]

nice to have you back M,

if changing the date format is the case, i think I shoud just delete the whoe row. Wil changing the date format conflict with the conditional statements of the amber or red?
I was thinking of just seperating the condition. A new rule for each, what i mean is seperate rule for the of row completed, and then another rule for if > = 30 days before TODAY....

what do you think?

I have posted this problem in other forums, but none of the replies have been helpfull....

 

[Marcelo Branco]

nice to have you back M,

if changing the date format is the case, i think I shoud just delete the whoe row. Wil changing the date format conflict with the conditional statements of the amber or red?
I was thinking of just seperating the condition. A new rule for each, what i mean is seperate rule for the of row completed, and then another rule for if > = 30 days before TODAY....

what do you think?

I have posted this problem in other forums, but none of the replies have been helpfull....

The date format doesnt matter - Excel stores dates as serial numbers. The format is only a convenient way to show the dates.

I'm not following what are you trying to change or what you mean with
"A new rule for each.... separate rule for the of row completed..."

The rules work fine as they are...

M.

 

[drey_d]

Hi M,

I think I'm happy with the results. Thank you for your help! I've moved on to Access now.. applying the excel formula soon by extracting it from my database... probably be starting another thread soon..

 

[markmzz]

I did some test and the Marcelo's formulas are working here. Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Milestone</td><td style="font-weight: bold;;">Deadline</td><td style="font-weight: bold;;">Status</td><td style="font-weight: bold;text-align: center;;">First - CF</td><td style="font-weight: bold;text-align: center;;">Second - CF</td><td style="font-weight: bold;text-align: center;;">Third - CF</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #92D050;;">PHASE1</td><td style="text-align: right;background-color: #92D050;;">10/30/2011</td><td style="background-color: #92D050;;">Planned</td><td style="text-align: center;background-color: #FF0000;;">=AND($B2-TODAY()<=10,$C2="Planned")</td><td style="text-align: center;background-color: #D99795;;">=AND($B2-TODAY()>=15,$B2-HOJE()<30,$C2="Planned")</td><td style="text-align: center;background-color: #92D050;;">=OR($B2-TODAY()>=30,$C2="Completed")</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #D99795;;">PHASE2</td><td style="text-align: right;background-color: #D99795;;">9/30/2011</td><td style="background-color: #D99795;;">Planned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FF0000;;">PHASE3</td><td style="text-align: right;background-color: #FF0000;;">9/12/2011</td><td style="background-color: #FF0000;;">Planned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FF0000;;">PHASE4</td><td style="text-align: right;background-color: #FF0000;;">9/20/2011</td><td style="background-color: #FF0000;;">Planned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #92D050;;">PHASE5</td><td style="text-align: right;background-color: #92D050;;">9/17/2011</td><td style="background-color: #92D050;;">Completed</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;;">Milestone</td><td style="font-weight: bold;;">Deadline</td><td style="font-weight: bold;;">Status</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="background-color: #92D050;;">PHASE1</td><td style="text-align: right;background-color: #92D050;;">10/30/2011</td><td style="background-color: #92D050;;">Planned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #D99795;;">PHASE2</td><td style="text-align: right;background-color: #D99795;;">9/30/2011</td><td style="background-color: #D99795;;">Planned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FF0000;;">PHASE3</td><td style="text-align: right;background-color: #FF0000;;">9/12/2011</td><td style="background-color: #FF0000;;">Planned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #92D050;;">PHASE4</td><td style="text-align: right;background-color: #92D050;;">9/20/2011</td><td style="background-color: #92D050;;">Completed</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="background-color: #92D050;;">PHASE5</td><td style="text-align: right;background-color: #92D050;;">9/17/2011</td><td style="background-color: #92D050;;">Completed</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: center;;">Date Format</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: center;;">m/d/yyyy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />
Markmzz

 

[Marcelo Branco]

Hi M,

I think I'm happy with the results. Thank you for your help! I've moved on to Access now.. applying the excel formula soon by extracting it from my database... probably be starting another thread soon..

You are welcome and tks for the feedback

M.