If there is an "instance" which spans two months (end of month1, start of
month2) which month is it recorded against?

In the pattern below, how many instances are there given there are 3 sets of
"S"s but each is within 5 days of the previous?

s
blank
blank
blank
blank
s
s
s
s
blank
blank
blank
blank
s
s


"louiscourtney" wrote:

I have a data base that records the amount of holidays and sickness taken
each month and then colates all the info into a summery sheet

What I'm after is a macro or formula that totals up the amount of sick
instances each month and gives me a running total for the year

I will try to explain how it is set up
Each month has a differerant tab
In column A from A4 to A100 are a list of names
In Row 3 Column D to AH are a list of all the dates 1st to 31st
In each of the rows i record either a H for Holiday or an S for sickness
The problem i have is that everyone is on differant shift patterns (4 on 4
off or days mon to fri) so will need it tp reconises that if the gap is more
than 5
days part from the last instance then it is a new instance if it is less than
5 then it's the same instance

For example
I would like the answer for the following to return 3 Sick instances
D4 = H
E4 = Blank
F4 = Blank
G4 = Blank
H4 = Blank
I4 = S
J4 = S
K4 = Blank
L4 = Blank
M4 = Blank
N4 = Blank
O4 = Blank
P4 = Blank
S4 = S
T4 = S
U4 = Blank
V4 = Blank
W4 = Blank
X4 = Blank
Y4 = Blank
Z4 = Blank
AA4 = Blank
AB4 = S
AC4 = S
AD4 = blank
AE4 = Blank
AF4 = Blank
AG4 = Blank
AH4 = Blank

I also need it to somehow look at the end of each month to capture whether
it is still the same or new instances
I can create a new tab to include the answers, set up the same way with the
same names