Hello everyone

What I have is a sheet call 'Late" with all staff names down column A
(starting cell A2) and the date from B1 to IV1.
Now assuming the person's name in cell A2 is Anna and the first date in
cell B1 is the 01-06-2006 and then 02-06-2006 and so forth.
Now if Anna is 15 minutes late on the 03-06-2006 I would then enter the
number 15 into the cell D2 and if she is again 20 minutes late on the
15-06-2006 I would then enter the number 20 into cell P2 and if she was
again 30 minutes late on the 20-06-2006 I would then enter the number 30
into cell U2. Now what I would like to do is use a condition format to
have all cells highlighted to the colour red and the font white if she
is late 3 times or more in any 35 day period. So in other words in
Anna's case I would like all cells from D2 to U2 to change to red.
Now I have tried the following formular in the conditional formatting
=COUNT(B2:AJ2,B2)3 and then dragged it accross to IV2. It will change
to red for me from the 1st late to the 2nd late and in between and also
the cell where the last late is, but it will not change colour between
the 2nd late and the 3rd late. I am not sure if this is even possible
but I am hoping that there might be someone out there that might just
have a solution to this problem.

Thanks

Paul Maynard
Moscow
Russia


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=555488

Nel post
*paulrm906* ha scritto:

Hello everyone

What I have is a sheet call 'Late" with all staff names down column A
(starting cell A2) and the date from B1 to IV1.
Now assuming the person's name in cell A2 is Anna and the first date
in cell B1 is the 01-06-2006 and then 02-06-2006 and so forth.
Now if Anna is 15 minutes late on the 03-06-2006 I would then enter
the number 15 into the cell D2 and if she is again 20 minutes late on
the 15-06-2006 I would then enter the number 20 into cell P2 and if
she was again 30 minutes late on the 20-06-2006 I would then enter
the number 30 into cell U2. Now what I would like to do is use a
condition format to have all cells highlighted to the colour red and
the font white if she is late 3 times or more in any 35 day period.
So in other words in Anna's case I would like all cells from D2 to U2
to change to red.
Now I have tried the following formular in the conditional formatting
=COUNT(B2:AJ2,B2)3 and then dragged it accross to IV2. It will change
to red for me from the 1st late to the 2nd late and in between and
also the cell where the last late is, but it will not change colour
between the 2nd late and the 3rd late. I am not sure if this is even
possible but I am hoping that there might be someone out there that
might just have a solution to this problem.

Hi Paul,

To start solving your problem, select B2, then menu Format, Conditional
Formatting, chose Formula Is under Condition 1 then input this formula:

=COUNT($B2:B2)3

(be aware of "$"...)

click Format, choose your formatting, then click OK twice.

Now you can drag B2 by rows and columns. In this way when you'll have more
than 3 lates in a row, from the fourth the cells will be formatted as you
specified.

This is just a first step, because I have to think if is possible (and how)
the thing with the 35 days...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

I think you are along the right lines count(b2:aj2)=3

This will apply in cell aj2 and look at this cell and the preceding 34
cells.
but for the cells up to this cell it needs to be count ($b2:b2)3 then
copied accross

You also wish to look at the cell and the following 34 cells as well
so you need
count(b2:aj2)=3 in cell b2 as the other condition. It can then also be
copied accross

In both instances format the conditional formating to turn the cells
red and it should work

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=555488

Hello Dav and Franz,

First of all thanks for your response, I have tried what you have both
suggested and what you said Dav about using Franz’s suggestion for the
first 34 and then changing it slightly made good sense as soon as I had
read it and to be honest with you it was something that I have no even
thought of. I have already tried using countif(b2:aj2)=3 plus a few
other ideas but kept running into problems. Ok now to get back to what
you guys suggested. Ok I have tried it but again a few problems;
1st Franz I changed your suggestion to 2 rather then 3.
2nd Everything appears to work good but after entering the 3rd late
all the cells change colour from the last entry to forward. I will try
to explain a little bit better. Ok for example if Anna is late on the
8th (cell I2) and 10th (cell K2) June and then again late on the 11th
(cell L2) June all the cells from the 11th June to the 12th (cell AQ2)
July change colour. But the cells from the 8th June and up to the cell
before the last late do not change colour. But if you count all the
cells from the 1st late to the last coloured cell 8th July it will
equal 35 exactly, so I thought that is a good start.
But now after trying all of this I thought of a good idea and that is
if we can not get the above to work perfect. I will go with the
following. So if I changed the formula to be 1. Now what happens is if
someone is late 2 times all the cells will light up from the last late
for example if I use the example above (with the 3 lates) to the 8th
July and the managers then give that staff member a bit of a warning
“If you are late any time now during this period which is highlighted
you will be receiving a fine”.
But of cause if you guys can come up with a good solution I will give
it ago. But to be honest with you I have been working on this now for
sometime and I normally do not give up. But both you guys have put me
onto the right track and for that thanks very much.

Paul Maynard


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=555488