Instances of sick (apologies to those that have answered it before

[louiscourtney]

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

 

[Toppers]

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

 

[Toppers]

Further questions: working on the NAAFOF principle (Never Assume Anything,
Find Out First):

Are shifts always in the same week i.e cannot overlap two weeks (e.g.
Thursday, Friday, Monday, Tuesday)?

If so, is an "instance" therefore any sickness which occurs in a given week?

 

[louiscourtney]

Topper
Shifts can overlapp weeks Friday saturday sunday monday etc
Looking at instances over the month and maybe a running total for the year
Regards
Shane

 

[Toppers]

.... and answer to my first posting re number of instances in the following ..

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]

There are 3 instances and a total of 6 days (maybe if i showed differantly)
as below

Jan 01 = H
Jan 02 = Off shift
Jan 03 = Off Shift
Jan 04 = Off Shift
Jan 05 = Off shift
Jan 06 = S
Jan 07 = S
Jan 08 = Worked
Jan 09 = Worked
Jan 10 = Off shift
Jan 11 = Off shift
Jan 12 = Off shift
Jan 13 = Off shift
Jan 14 = S
Jan 15 = worked
Jan 16 = Worked
Jan 17 = Worked
Jan 18 = off shift
Jan 19 = off shift
Jan 20 = off shift
Jan 21 = off shift
Jan 22 = worked
Jan 23 = worked
Jan 24 = S
Jan 25 = S
Jan 26 = off shift
Jan 27 = off shift
Jan 28 = off shift
Jan 29 = off shift
Jan 30 = S
Jan 31 = Worked

 

[louiscourtney]

Maybe if it is shown like this you can understand it clearer?
I need the answer to show 3 instances / 6 days, then the promblem is when
it goes into the next month and i have no idea as to this
Thanks

Jan 01 = H
Jan 02 = Off shift
Jan 03 = Off Shift
Jan 04 = Off Shift
Jan 05 = Off shift
Jan 06 = S
Jan 07 = S
Jan 08 = Worked
Jan 09 = Worked
Jan 10 = Off shift
Jan 11 = Off shift
Jan 12 = Off shift
Jan 13 = Off shift
Jan 14 = S
Jan 15 = worked
Jan 16 = Worked
Jan 17 = Worked
Jan 18 = off shift
Jan 19 = off shift
Jan 20 = off shift
Jan 21 = off shift
Jan 22 = worked
Jan 23 = worked
Jan 24 = S
Jan 25 = S
Jan 26 = off shift
Jan 27 = off shift
Jan 28 = off shift
Jan 29 = off shift
Jan 30 = S
Jan 31 = Worked

 

[Toppers]

.. and this .... (you are ducking the difficult !) ....

Is 14th to 31st ONE instance as there are NO "gaps" > 5 days (17th - 22nd
=5 ,25th - 30th = 5)

Sorry to be so pedantic .....

Jan 01 = H
Jan 02 = Off shift
Jan 03 = Off Shift
Jan 04 = Off Shift
Jan 05 = Off shift
Jan 06 = S
Jan 07 = S
Jan 08 = Worked
Jan 09 = Worked
Jan 10 = Off shift
Jan 11 = Off shift
Jan 12 = Off shift
Jan 13 = Off shift
Jan 14 = S
Jan 15 = worked
Jan 16 = Worked
Jan 17 = S
Jan 18 = off shift
Jan 19 = off shift
Jan 20 = off shift
Jan 21 = off shift
Jan 22 = S
Jan 23 = worked
Jan 24 = S
Jan 25 = S
Jan 26 = off shift
Jan 27 = off shift
Jan 28 = off shift
Jan 29 = off shift
Jan 30 = S
Jan 31 = Worked

 

[louiscourtney]

No it will be two instances as they came into work on the 15th
Sorry to be so difficult !!

 

[Toppers]

Please e-mail me and I will send a sample w/book to check if monthly
calculation is correct.

toppers <at> NOSPAMjohntopley.fsnet.co.uk

 

[louiscourtney]

comes back as error
try (e-mail address removed)