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" wrote in message
...
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.



"Mark" wrote:

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.