Occasions in a sickness record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a sickness record with each month on a different page,
there is no information from day to day unless someone is sick then I enter
an 'S' for the amount of days they are of i.e. s,s,s,s,o,o,o,s,s,s,s
I can total the number of sick days but want to calculate the number of
occasions the user has had off. in this case 2 occasions, made up of 4 days
sick 3 days in and another 4 days off sick.

The formula would have to cover a running total so that I can account for
the total in the year, i.e. looking at other pages of past months.

Hope you can help

Many thanks in advanced.
 
Rob,

Here is an outline of the approach. Have twelve identical worksheets,
one for each month, and one summary sheet. In the monthly sheets list
all employees in column A, use column B for sickness days (a simple
COUNTIF formula - see below) and column C for sickness occasions - this
will need to be a User Defined Function (UDF). You can then have up to
31 columns for the days in that month.

Do all your staff work the same days (eg Monday to Friday)? If so, then
you can leave the weekends out of the sheet, but if not, then you will
have to use some character (eg "x") to indicate that the member of
staff was not expected in on a particular day, so that if someone was
off from Friday to Tuesday, this would not be counted as 2 occasions -
you would show it as S x x S S. All the other cells could be left
blank, so that you don't have a mass of data entry to do.

On your summary sheet you would have the same list of names and in
column B a formula like:

=SUM(Jan:Dec!B4)

would add up all your sickness days for the whole year for each member
of staff. A similar formula would add up the occasions in column C. The
UDF on the monthly sheets would scan the columns from D to AH and count
all transitions from S to blank as being a sickness occasion. Here's
an example of a UDF to do this:

Function occasions(days As Range) As Integer
occasions = 0
If Range("B" & days.Row).Value = 0 Then Exit Function
For i = 1 To 31
If days(i).Value = "S" And _
days(i + 1).Value = "" Then occasions = occasions + 1
Next i
End Function

You would use this, for example in cell C4 as:

=occasions(D4:AI4)

and the formula in B4 would be:

=COUNTIF(D4:AH4,"S")

Note that the range for the occasions formula extends to AI. This does
not check previous or following months' sheets - rather, it treats each
month separately. This may overstate the occasions, as a person who was
off sick on the last day of one month and the first day of the next
month would be counted as having 2 occasions of sickness. Perhaps
someone else can advise you of a way around this, as I'm going on
holiday first thing on Wednesday morning and don't have time to look
into this at the moment.

You would copy these two formulae down columns B and C for as many
names as you have on the sheet. If you have new staff joining, just add
their names to the bottom of the sheets from that month onwards (plus
on the summary sheet) and copy the formulae down further.

Hope this helps.

Pete
 
That works Great and thanks for the Formula,
There are a few things that it doesn't pick up on and I don't know what to
add to the formula to do this.

The sickness record also has Holiday entries in it. H= Holiday (Complicated
I know:) so where I have an 'S' followed by a 'H' I need to count it as an
occasion.
or even a H,S,H or any combination of it.
Also it needs to recognise lower case characters s and h .

Does any one know what I can do to get a running total from the seperate
pages so that if someone was sick on the last day of the month and the first
day of the new month it only counts 1 occasion sick.

Many thanks in advanced again.

Rob-WNS
 
Hi Rob,

I've done my packing so I can help a bit more. The algorithm I gave you
was a very simple one based on what you said in your first post - in
real life things tend to be a bit messier. I've modified the UDF, as
follows:

Function occasions(days As Range) As Integer
Dim sickness As Boolean
sickness = False
occasions = 0
If Range("B" & days.Row).Value = 0 Then Exit Function
For i = 1 To 31
If UCase(days(i).Value) = "S" Then sickness = True
If sickness And days(i + 1).Value = "" Then
occasions = occasions + 1
sickness = False
End If
Next i
End Function

By introducing a variable sickness which is set to True when a period
of sickness starts and is only reset when a blank entry is encountered,
this enables other character codes to be used - I suggested "x" for not
expected, and now you have introduced "H" or "h". Only a blank will
reset sickness (and thus trigger the occasion to be incremented), so
you can use any other codes you wish to record other events. Lower case
s is converted to S in the comparison, so both of these can be used.

If you wanted to take account of sickness from the end of one month
into the next, then you could insert a new column D into each of the
monthly sheets and set up a formula in each month (not the first one)
to look back at the previous month and if there was S or s in the final
day then set the cell to True. Then in the UDF you could check after
the Next i statement to see if that cell was True AND the first day of
the current month was also S, and if so reduce occasions by 1 as you
will already have counted it in the previous month.

Another problem you will face, though, is having months with different
days, as the For loop cycles through 31 cells each time. You could
think about having two half-yearly sheets rather than 12 monthly
sheets, and this will simplify things. Obviously, you would pass a
larger range into the function, and the loop counter would go to 183.
Your summary sheet would also be a lot simpler.

I don't expect this thread to still be current in two week's time, so I
hope this gets you somewhere further on.

Pete
 
Back
Top