L
louiscourtney
I have a spread sheet that records sickness levels, what I'm after is a
formula or maybe even a macro that will return the about of sick instances
plus amount of days. The problem is that it needs to somehow have a bit that
due to shift patterns of 4 on 4 off 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
Example
At the start of D3 running right out are the days of the month starting from
Jan 01 to Dec 31st
In A4 down to A100 are a list of names
If i use January as an example i need the answer to come to 3 instances and
a total of 12 days off
Cell
D4 = s
E5 = s
F6 = s
G7 = blank
H8 = Blank
I9 = blank
J10 = Blank
K11 = Blank
L12 = Blank
M13 = Blank
N14 = Blank
O15 = s
P16 = Blank
Q17 = Blank
R18 = Blank
S19 = Blank
T20 = s
U21 = s
V22 = s
W23 = s
X24 = blank
Y25 = blank
Z26 = blank
AA27 = blank
AB28 = s
AC29 = s
AD30 = s
AE31 = s
Hope this makes sense
formula or maybe even a macro that will return the about of sick instances
plus amount of days. The problem is that it needs to somehow have a bit that
due to shift patterns of 4 on 4 off 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
Example
At the start of D3 running right out are the days of the month starting from
Jan 01 to Dec 31st
In A4 down to A100 are a list of names
If i use January as an example i need the answer to come to 3 instances and
a total of 12 days off
Cell
D4 = s
E5 = s
F6 = s
G7 = blank
H8 = Blank
I9 = blank
J10 = Blank
K11 = Blank
L12 = Blank
M13 = Blank
N14 = Blank
O15 = s
P16 = Blank
Q17 = Blank
R18 = Blank
S19 = Blank
T20 = s
U21 = s
V22 = s
W23 = s
X24 = blank
Y25 = blank
Z26 = blank
AA27 = blank
AB28 = s
AC29 = s
AD30 = s
AE31 = s
Hope this makes sense