In cells B1:B47 are times in decending order, military time. (23:30, 23:00,
22:30.....:30)

I've set up the sheets so I can enter Start time in C50 and end time in C51
and the cells next to the appropriate times in B will get an "X" and turn
green. ie =IF(C$50$B30,""IF(C$51=$B30"X","")) and CF to turn cells with an
X green.

In C59 I have the formula =COUNTIF(C2:47,"X") and in C60 I have
=NameofPriorSheet!C60=C59 to get a running total of X's....thus 80 X's equals
40 hours worked, anything over is overtime.

My Question - I need to add a 2nd Conditional Formating to turn Only the
cells with an "X" AND represent cells that put the COUNTA of X's over 80 to
turn RED. Also, the cells would have to be the lesser numbered rows.

For instance - On one sheet the COUNTA of X's is 86 and cells C12 through
C21 have X's in green cells. I need the X's in cells C12 through C17 to turn
Red, with an X.

"carrera" wrote:

In cells B1:B47 are times in decending order, military time. (23:30, 23:00,
22:30.....:30)

I've set up the sheets so I can enter Start time in C50 and end time in C51
and the cells next to the appropriate times in B will get an "X" and turn
green. ie =IF(C$50$B30,""IF(C$51=$B30"X","")) and CF to turn cells with an
X green.

In C59 I have the formula =COUNTIF(C2:47,"X") and in C60 I have
=NameofPriorSheet!C60=C59 to get a running total of X's....thus 80 X's equals
40 hours worked, anything over is overtime.

My Question - I need to add a 2nd Conditional Formating to turn Only the
cells with an "X" AND represent cells that put the COUNTA of X's over 80 to
turn RED. Also, the cells would have to be the lesser numbered rows.

For instance - On one sheet the COUNTA of X's is 86 and cells C12 through
C21 have X's in green cells. I need the X's in cells C12 through C17 to turn
Red, with an X.



Before I answer this question there are two things I must know.

1. Are you getting paid to do this?

2. Why are you reinventing the wheel?

There must be a gazillion templates floating around for timekeeping. I know
it's fun to make your own but if we solve the problem for you, then you don't
have any fun, do you?

B+
HALinNY

I find your response inappropriate and rude. Are you employed by Microsoft
Office Online, or are you another random person in the community?

To address your "questions"
Am I getting paid to do this?....I frankly don't know what you mean by that
question, and I don't see where it has any bearing on my question.

Reinventing the wheel, I did not know that is what I was doing. We have
an automated timekeeping system, I am trying to ascertain what times of the
day people are incuring the most overtime, and need a visual to show those
responsible for scheduling. However, again, I don't see where this has any
bearing on my question, and you are making assumptions regarding why I was
asking the question. I did not realize my question had to pass some kind of
litmus test for worthiness.

It is obvious I have done quite a bit of the work on my own, and I am
seeking this last puzzle piece. I have endevored to solve this, and have made
my best effort. That is why I have turned to this forum.

So, now that I have complied to your rude and unnecessary question, do you
or do you not know of a solution to my question?

"HALinNY" wrote:



"carrera" wrote:

In cells B1:B47 are times in decending order, military time. (23:30, 23:00,
22:30.....:30)

I've set up the sheets so I can enter Start time in C50 and end time in C51
and the cells next to the appropriate times in B will get an "X" and turn
green. ie =IF(C$50$B30,""IF(C$51=$B30"X","")) and CF to turn cells with an
X green.

In C59 I have the formula =COUNTIF(C2:47,"X") and in C60 I have
=NameofPriorSheet!C60=C59 to get a running total of X's....thus 80 X's equals
40 hours worked, anything over is overtime.

My Question - I need to add a 2nd Conditional Formating to turn Only the
cells with an "X" AND represent cells that put the COUNTA of X's over 80 to
turn RED. Also, the cells would have to be the lesser numbered rows.

For instance - On one sheet the COUNTA of X's is 86 and cells C12 through
C21 have X's in green cells. I need the X's in cells C12 through C17 to turn
Red, with an X.



Before I answer this question there are two things I must know.

1. Are you getting paid to do this?

2. Why are you reinventing the wheel?

There must be a gazillion templates floating around for timekeeping. I know
it's fun to make your own but if we solve the problem for you, then you don't
have any fun, do you?

B+
HALinNY

"carrera" wrote:

I find your response inappropriate and rude. Are you employed by Microsoft
Office Online, or are you another random person in the community?

To address your "questions"
Am I getting paid to do this?....I frankly don't know what you mean by that
question, and I don't see where it has any bearing on my question.

Reinventing the wheel, I did not know that is what I was doing. We have
an automated timekeeping system, I am trying to ascertain what times of the
day people are incuring the most overtime, and need a visual to show those
responsible for scheduling. However, again, I don't see where this has any
bearing on my question, and you are making assumptions regarding why I was
asking the question. I did not realize my question had to pass some kind of
litmus test for worthiness.

It is obvious I have done quite a bit of the work on my own, and I am
seeking this last puzzle piece. I have endevored to solve this, and have made
my best effort. That is why I have turned to this forum.

So, now that I have complied to your rude and unnecessary question, do you
or do you not know of a solution to my question?


My apologies. I was just trying to be avuncular and you misunderstood me.

So if I understand this correctly, you have a workbook where each sheet
covers one day and you accumulate Xs from the beginning of the work week,
day-by-day, to the end of the work week. A green X represents a half-hour of
regular time and a red X would indicate a half-hour of overtime. You use a
40-hour week and there is no time deducted for breaks or lunch.

What you need to do is attach a serial number to each X that you generate
based on the start and stop times. That is to say that each half-hour
increment that is accounted for is assigned a number and if the number is
below 81, the X is green; otherwise it is red.

My recommendation would be to put all workdays on a single sheet because it
makes counting the allocated increments much easier and less error-prone
because moving something around on one sheet could affect all the others in
ways you did not anticipate.

Then lay out the days and hours in a matrix that facilitates the counting of
preceding Xs which would give you the serial number you need to decide if the
X should be green or red.

I don't want to go any further at this point because I may have made an
incorrect assumption or you might not need any more help.

And to answer your other questions, I do not work for Microsoft. I am just
one of those people who have been using EXCEL for about 20 years now. And
when you say you are seeking the last puzzle piece, what you really mean is
that you have painted yourself into a corner. And your taking umbrage and
copping an attitude when someone tries to help you only proves that I am
right.

So please let me know if there is anything else I can do for you, :)

B+
HALinNY

No, I have not painted myself into a corner. I simply have not thought it
necessary to bring up the other properties of the workbook, since they were
not relevant to the question.

I do not want all workdays on a single sheet, as in reality I am not merely
discussing "column C", each sheet is going to track 40 plus people, one
column per person. I want each sheet to be a snapshot of the entire day, so
any person can look at it and see, for instance, that 3 people were there at
2AM, all 40 people where working at 10AM, that 10 people were working at 7PM,
3 people at 11:30pm, etc. the reason I want those cells colored is to show
each persons presence or non-presence. I need 7 days to show the fluctuations
each day, and when certain people are going into overtime. At that point, I
want those cells to be red, not green.
I have no problem with working with multiple sheets, and carrying formulas
over.
This workbook is not for payroll purposes, and doesn't have to be that
exact, as evidenced by the fact I'm willing to account for time in broad 1/2
hour increments. My purpose is illustrative, using a broad brush. I am trying
to show others in a broad way when we have people working that would be
better utilized at another time.

Your proposal sound like it would work....however, I don't know the process
of assigning a serial number to a cell.

How would one "assign a serial number to each X"?

"HALinNY" wrote:



"carrera" wrote:

I find your response inappropriate and rude. Are you employed by Microsoft
Office Online, or are you another random person in the community?

To address your "questions"
Am I getting paid to do this?....I frankly don't know what you mean by that
question, and I don't see where it has any bearing on my question.

Reinventing the wheel, I did not know that is what I was doing. We have
an automated timekeeping system, I am trying to ascertain what times of the
day people are incuring the most overtime, and need a visual to show those
responsible for scheduling. However, again, I don't see where this has any
bearing on my question, and you are making assumptions regarding why I was
asking the question. I did not realize my question had to pass some kind of
litmus test for worthiness.

It is obvious I have done quite a bit of the work on my own, and I am
seeking this last puzzle piece. I have endevored to solve this, and have made
my best effort. That is why I have turned to this forum.

So, now that I have complied to your rude and unnecessary question, do you
or do you not know of a solution to my question?


My apologies. I was just trying to be avuncular and you misunderstood me.

So if I understand this correctly, you have a workbook where each sheet
covers one day and you accumulate Xs from the beginning of the work week,
day-by-day, to the end of the work week. A green X represents a half-hour of
regular time and a red X would indicate a half-hour of overtime. You use a
40-hour week and there is no time deducted for breaks or lunch.

What you need to do is attach a serial number to each X that you generate
based on the start and stop times. That is to say that each half-hour
increment that is accounted for is assigned a number and if the number is
below 81, the X is green; otherwise it is red.

My recommendation would be to put all workdays on a single sheet because it
makes counting the allocated increments much easier and less error-prone
because moving something around on one sheet could affect all the others in
ways you did not anticipate.

Then lay out the days and hours in a matrix that facilitates the counting of
preceding Xs which would give you the serial number you need to decide if the
X should be green or red.

I don't want to go any further at this point because I may have made an
incorrect assumption or you might not need any more help.

And to answer your other questions, I do not work for Microsoft. I am just
one of those people who have been using EXCEL for about 20 years now. And
when you say you are seeking the last puzzle piece, what you really mean is
that you have painted yourself into a corner. And your taking umbrage and
copping an attitude when someone tries to help you only proves that I am
right.

So please let me know if there is anything else I can do for you, :)

B+
HALinNY

"carrera" wrote:

No, I have not painted myself into a corner. I simply have not thought it
necessary to bring up the other properties of the workbook, since they were
not relevant to the question.

I do not want all workdays on a single sheet, as in reality I am not merely
discussing "column C", each sheet is going to track 40 plus people, one
column per person. I want each sheet to be a snapshot of the entire day, so
any person can look at it and see, for instance, that 3 people were there at
2AM, all 40 people where working at 10AM, that 10 people were working at 7PM,
3 people at 11:30pm, etc. the reason I want those cells colored is to show
each persons presence or non-presence. I need 7 days to show the fluctuations
each day, and when certain people are going into overtime. At that point, I
want those cells to be red, not green.
I have no problem with working with multiple sheets, and carrying formulas
over.
This workbook is not for payroll purposes, and doesn't have to be that
exact, as evidenced by the fact I'm willing to account for time in broad 1/2
hour increments. My purpose is illustrative, using a broad brush. I am trying
to show others in a broad way when we have people working that would be
better utilized at another time.

Your proposal sound like it would work....however, I don't know the process
of assigning a serial number to a cell.

How would one "assign a serial number to each X"?


I have a couple of ideas that I will post here after I have my lunch. Be
back in about an hour.

B+
HALinNY

"carrera" wrote:

No, I have not painted myself into a corner. I simply have not thought it
necessary to bring up the other properties of the workbook, since they were
not relevant to the question.

I do not want all workdays on a single sheet, as in reality I am not merely
discussing "column C", each sheet is going to track 40 plus people, one
column per person. I want each sheet to be a snapshot of the entire day, so
any person can look at it and see, for instance, that 3 people were there at
2AM, all 40 people where working at 10AM, that 10 people were working at 7PM,
3 people at 11:30pm, etc. the reason I want those cells colored is to show
each persons presence or non-presence. I need 7 days to show the fluctuations
each day, and when certain people are going into overtime. At that point, I
want those cells to be red, not green.
I have no problem with working with multiple sheets, and carrying formulas
over.
This workbook is not for payroll purposes, and doesn't have to be that
exact, as evidenced by the fact I'm willing to account for time in broad 1/2
hour increments. My purpose is illustrative, using a broad brush. I am trying
to show others in a broad way when we have people working that would be
better utilized at another time.

Your proposal sound like it would work....however, I don't know the process
of assigning a serial number to a cell.

How would one "assign a serial number to each X"?

"HALinNY" wrote:


You currently have some formula that will place an X in the cell if a set of
conditions is met. The X is merely a marker; it has no intrinsic value and
can be replaced with any other marker. I suggest the formula now looks like
this...

=IF(<logical-expression,"X","")

Change the formula to this ...

=IF(<logical-expression,MAX(c$1:cx)+1,0) where c is this column and x=the

number of the row above.
Thus, if the formula was in cell G35, it would be...

=IF(<logical-expression,MAX(G$1:G34)+1,0)

By doing this you should get a serial number if the time segment is active.
You will have to work out how to begin the value with 0 on the first sheet
and carry it over to the next day, every day, but by using the MAX() function
it should be fairly straightforward.

So when you are all done with entering the times worked you will have for
each employee a column with a lot of zeros and 16 or so consecutive numbers
for an 8 hour day.

Now for the conditional formatting of each cell that represents a time
segment worked.

If the value in the cel is 0, format white text and white pattern.

If the value is between 1 and 80, format green text and green pattern.

If the value is greater than 80, format red text and red pattern.

The values will "disappear" and the entire cell will assume the color you
intended. This should look better than just having an X.

That's all for now. I want to make sure you understand this and get it
working before going any further.

B+
HALinNY

"carrera" wrote:

Back on task here, how do you assign a serial # to a particular cell?

It's obvious by now I have a good idea of what I'm trying to accomplish, and
simply require an assist. If you give me all the options you can think of,
I'm confident I will find the one that best suits. That would seem to be more
efficient than these "getting to know you" posts. You'll have to take my word
for it that I know what I want.

Actually, if there is an actual employee of microsoft that can address this
without the commentary, dare I hope for an MVP, I'd be grateful. I'm trying
to work here, not get the 3rd degree.

"HALinNY" wrote:

Carrera, you have to be more careful when you read the thread. The latest
post is not always at the bottom.

I posted the following response to you at 1342:

You currently have some formula that will place an X in the cell if a set of
conditions is met. The X is merely a marker; it has no intrinsic value and
can be replaced with any other marker. I suggest the formula now looks like
this...

=IF(<logical-expression,"X","")

Change the formula to this ...

=IF(<logical-expression,MAX(c$1:cx)+1,0) where c is this column and x=the

number of the row above.
Thus, if the formula was in cell G35, it would be...

=IF(<logical-expression,MAX(G$1:G34)+1,0)

By doing this you should get a serial number if the time segment is active.
You will have to work out how to begin the value with 0 on the first sheet
and carry it over to the next day, every day, but by using the MAX() function
it should be fairly straightforward.

So when you are all done with entering the times worked you will have for
each employee a column with a lot of zeros and 16 or so consecutive numbers
for an 8 hour day.

Now for the conditional formatting of each cell that represents a time
segment worked.

If the value in the cel is 0, format white text and white pattern.

If the value is between 1 and 80, format green text and green pattern.

If the value is greater than 80, format red text and red pattern.

The values will "disappear" and the entire cell will assume the color you
intended. This should look better than just having an X.

MVPs are not Microsoft employees. You have to PAY for Microsoft to help you
unless you within your initial assistance period or you have some kind of
arrangement with them. That is why Microsoft sponsors these forums.

B+
HALinNY

"HALinNY" wrote:



"carrera" wrote:

Back on task here, how do you assign a serial # to a particular cell?

It's obvious by now I have a good idea of what I'm trying to accomplish, and
simply require an assist. If you give me all the options you can think of,
I'm confident I will find the one that best suits. That would seem to be more
efficient than these "getting to know you" posts. You'll have to take my word
for it that I know what I want.

Actually, if there is an actual employee of microsoft that can address this
without the commentary, dare I hope for an MVP, I'd be grateful. I'm trying
to work here, not get the 3rd degree.

"HALinNY" wrote:


"carrera" wrote:

Back on task here, how do you assign a serial # to a particular cell?

It's obvious by now I have a good idea of what I'm trying to accomplish, and
simply require an assist. If you give me all the options you can think of,
I'm confident I will find the one that best suits. That would seem to be more
efficient than these "getting to know you" posts. You'll have to take my word
for it that I know what I want.

Actually, if there is an actual employee of microsoft that can address this
without the commentary, dare I hope for an MVP, I'd be grateful. I'm trying
to work here, not get the 3rd degree.

"HALinNY" wrote:

Carrera, you have to be more careful when you read the thread. The latest
post is not always at the bottom.

I posted the following response to you at 1342 and again at 1425 PST:

You currently have some formula that will place an X in the cell if a set of
conditions is met. The X is merely a marker; it has no intrinsic value and
can be replaced with any other marker. I suggest the formula now looks like
this...

=IF(<logical-expression,"X","")

Change the formula to this ...

=IF(<logical-expression,MAX(c$1:cx)+1,0) where c is this column and x=the

number of the row above.
Thus, if the formula was in cell G35, it would be...

=IF(<logical-expression,MAX(G$1:G34)+1,0)

By doing this you should get a serial number if the time segment is active.
You will have to work out how to begin the value with 0 on the first sheet
and carry it over to the next day, every day, but by using the MAX() function
it should be fairly straightforward.

So when you are all done with entering the times worked you will have for
each employee a column with a lot of zeros and 16 or so consecutive numbers
for an 8 hour day.

Now for the conditional formatting of each cell that represents a time
segment worked.

If the value in the cel is 0, format white text and white pattern.

If the value is between 1 and 80, format green text and green pattern.

If the value is greater than 80, format red text and red pattern.

The values will "disappear" and the entire cell will assume the color you
intended. This should look better than just having an X.

MVPs are not Microsoft employees. You have to PAY for Microsoft to help you
unless you within your initial assistance period or you have some kind of
arrangement with them. That is why Microsoft sponsors these forums.

B+
HALinNY

P.S. My explanation is based on Midnight being at the top of the column and
2400 being at the bottom. You have it inverted so you have to invert my
logic to make it work. That is you must take the MAX from the bottom of the
column and work towards the top.

B+
HALinNY