Counting Bradford score number of occasions?

[James Diaper]

Hi,

Please help.

We currently use an Excel spreadsheet to record holiday, sickness and absence for my staff and we use the Bradford score system to monitor attendance.

(Please see attachment of an example of the format)

I am able to use Countif to count the number of sick days but is there anyway I can count the number of occasions of sickness?

e.g. So if someone was off sick on Monday and Tuesday, came back to work on Wednesday but off sick again on Friday, that would be 3 days and 2 occasions.

At the moment I am calculating the occasions manually which can take time and also due to have a rolling 12 month score, it needs updating every month.

I really would like to do this automatically.

Your help would be much appreciated.

[#2]

Hi James,

By doing simple macro, we can make it possible. Doing it.

[#3]

Hi @James Diaper

Paste this macro. Hope it will work. 98%.

Sub Occa()

For r = 7 To 10
ct = 0
For i = 2 To 11
Cells(r, i).Select
a = Cells(r, i).Value
b = Cells(r, i + 1).Value
c = Cells(r, i - 1).Value
If a = "S" And b = "" Then
ct = ct + 1
End If
Next i
Cells(r, 19).Value = ct
Next r

End Sub