3 lates in any 35 day period.

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

Nel post news:[email protected]
*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

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