Can I have a cell change to red/green if not equal to:

[Mark]

Hi and Thanks for looking.

We are working on a job tracking worksheet and one of our guys has done
spectacular so far.

I'll state the simple, then ask the Question

there are 11 process's we track, left to right. 11 Phases and the 12th cell
is ship date with a total varience running.

Cells:
B5 = Begin Phase 1 date
B6 = Phase one complete date
B7 = is variance between dates B5 and B6, or date sarted and date complete.

I can allow 7 or less days for this phase.

Question:

If actual date in B6 exceeds (>7)days" and shows, lets say "8 days" in B7,
could we make B5 cell turn "red".????
If it is 7 days or less or equal to 7 turn green ? (In B5)

Now assuming that is a possible scenario:

Carry that a bit further.(please)

1. If B5 is red because because B7 is >7days.

We still have time to catch up in Phases 2-11 (each with a no greater than 7
day time limit.
Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49
or better, Can we turn the cells to green to show we are back on schedule.

I would sorta want the same for each cell.
If Phase 1 is 8 days and Phase 2 comes back in 5
I'm one day ahead and I would like C5 to turn green, and red and green along
those principals.

Is this possible?

Did I make any sense.?

Of course I would always want N5 to be red or green That is the ship date..

Job Tracking by phase.
I have the spread sheet done perfect to calculate the variance between cells
5 and 6. I just would like a visual color tracker between phases..

Any assistance would be great. AND if it can't be done just say so Thanks
very much.

Thank you,

Mark

Basically it's dates straight across. left to right
with actual finish dates increasing as they go left to right as each phase
takes time.

When I enter a completion date in each cell I want a color update red or
green.

 

[Ashish Mathur]

Hi,

You can solve the first part of the question by using conditional formatting

While in cell B5, go to format > conditional formatting and under formula is
type $B7>7 and select the font as red. Now click on Add and in formula is,
type $B7<=7 and select the font as green.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bernie Deitrick]

Mark,

Select B5:M7 (with B5 as the actice cell), format all the cells for green,
and then select Format / Conditional formatting...

Formula is...

with the formula

=B$6>($B$5+7*(COLUMN(B5)-1))

and then set your fill for red, and click OK.

HTH,
Bernie
MS Excel MVP

 

[Mark]

Bernie..WOW.. Thanks, I sorta understood Ashish's idea or formula which was
beyond me, however could you explain, why

Select B5:M7 ( where my N7 is the aggregate +/- ship date(c7 thru M7)

Should it read B5:N7 did I just write it wrong.

AND could you explain: =B$6>($B$5+7*(COLUMN(B5)-1))

Mostly the (COLUMN(B5)-1)) what does that do for this sheet.

Thank you very much

Mark



Thank you.

 

[Bernie Deitrick]

Mark,

Yes, use through N7 if column N follows the same pattern, but you may need
to change the formula logic a bit if column N is an "Aggregate"... Not sure
what that means in this context.

Basically, this part

=B$6>($B$5+7*(COLUMN(B5)-1))

means

IF the date in row 6 (note that the cell address in the formula is B$6,
which means the formula in each cell is evaluated against the value in row 6
of the same column) is greater than 7 times the number weeks (days) greater
than the date in cell B5 (note that the address is $B$5, so it is the same
for every cell when it is evaluated), then it is late.

Basically, my formula just adds 7 days for each column - this part:
COLUMN(B5)-1 returns the number of weeks (COLUMN(B5)-1 = 2 -1 = 1, then for
cells in C, COLUMN(C5)-1 returns 3-1 = 2 (weeks) ) - multiplied by 7 gives
you the days, and since one day in Excel = 1, adding to the original date
does the date math for you.

Have I explained that adequately?

HTH,
Bernie
MS Excel MVP

 

[Mark]

yes, vey much. You are so past me, I'm running to catch up. We will probably
change each of the phases to different day amounts so I may have to use the
first formula. some actually will be </> 7

When I wrote aggregate, I should have not tried to be so smart and said N7
shows the total +/- days based on how many days we put in each phase.,
between C7:M7

If I can Just get C7 to turn font red or green based on </>7 That would be a
huge plus.

Then I can work from there.

Thanks is not enuff here.

Mark

 

[Mark]

You are very appreciated:
If this works for us, this would be great.

I notice that a test date in
c5 of 01/01/2009 (Start)
to C6 of 01/08/2009 (end)
shows -7
is there a way to make this just 7
will the -7 force a problem in the font color change?

-8 or 8 red
-7 0r 7 green

does the minus matter?

Thank you very much.


If phase 1 is >7 and turns red
is <7 and is green

 

[Bernie Deitrick]

If each phase can have a different number of days, enter the allowed days in another row, say row 4,
and change the CF formula to

=B$6>($B$5+SUM($B$4:$B4))

HTH,
Bernie
MS Excel MVP