Hi,

This could be tricky to explain but I'll give a shot...

I have an absence tracker, the workbook contains a Summary sheet and 12
month sheets (January-December)

On the summary sheet..
I'm counting the number of sick days for each employee per month (Which is
easy enough) my boss wants to count the number of occurances (Where an
occurance is 1 individual sick day or if the employee is of sick for a
continous period)


Month sheet set-up is..

Employee names are in column C with the first name at row 10 and the sick
days, etc. are below that in row 11, the second name is in C12 and sick days
in C13 (continues down for 200 employees)and the dates are across the top,
with the day's at D8:AH8 (31 days in January) each of these sheets is set up
the same with the last column adjusted for the different number of day's in
each month.

So how can I count the number of occurances of sickness for each employee
for the year, with the results being displayed in the Summary sheet AL10 (for
the first employee, AL12 for the second employee, etc?

Hope that I have explained the problem enough.

Any help or advice on this would be appreciated.

Thanks in advance,

Gav.

Hi Gav
Try this,
Set conditional format for each day as follows; Cell Value is equal to ="S"
format the cell color or font color as you wish.
To enter sickness type the letter S into the correct cell. To enter if more
than one day merge cell then type the letter S
Put a column at the end of each month giving you the total of occassions
placing the following formula =COUNTIF (B5:AE5,"S") Change the range to suit.

"Gav123" wrote:

Hi,

This could be tricky to explain but I'll give a shot...

I have an absence tracker, the workbook contains a Summary sheet and 12
month sheets (January-December)

On the summary sheet..
I'm counting the number of sick days for each employee per month (Which is
easy enough) my boss wants to count the number of occurances (Where an
occurance is 1 individual sick day or if the employee is of sick for a
continous period)


Month sheet set-up is..

Employee names are in column C with the first name at row 10 and the sick
days, etc. are below that in row 11, the second name is in C12 and sick days
in C13 (continues down for 200 employees)and the dates are across the top,
with the day's at D8:AH8 (31 days in January) each of these sheets is set up
the same with the last column adjusted for the different number of day's in
each month.

So how can I count the number of occurances of sickness for each employee
for the year, with the results being displayed in the Summary sheet AL10 (for
the first employee, AL12 for the second employee, etc?

Hope that I have explained the problem enough.

Any help or advice on this would be appreciated.

Thanks in advance,

Gav.

"Gav123" skrev i en meddelelse
...
Hi,

This could be tricky to explain but I'll give a shot...

I have an absence tracker, the workbook contains a Summary sheet and 12
month sheets (January-December)

On the summary sheet..
I'm counting the number of sick days for each employee per month (Which is
easy enough) my boss wants to count the number of occurances (Where an
occurance is 1 individual sick day or if the employee is of sick for a
continous period)


Month sheet set-up is..

Employee names are in column C with the first name at row 10 and the sick
days, etc. are below that in row 11, the second name is in C12 and sick
days
in C13 (continues down for 200 employees)and the dates are across the top,
with the day's at D8:AH8 (31 days in January) each of these sheets is set
up
the same with the last column adjusted for the different number of day's
in
each month.

So how can I count the number of occurances of sickness for each employee
for the year, with the results being displayed in the Summary sheet AL10
(for
the first employee, AL12 for the second employee, etc?

Hope that I have explained the problem enough.

Any help or advice on this would be appreciated.

Thanks in advance,

Gav.

Hi

Asuming that "s" in actual cell indicateing a day sick

This routine will give you the number of periods an employee is sick in cell
AJ11 and down for each month.

Use the "Sum" function on your summary sheet to calculate the total sick
periods.

Option Explicit

Const Sick As String = "s"
Dim StartCell As Range
Dim LastColumn As Long
Dim Col As Long
Dim r As Long
Dim IsSick As Long
Dim Test As String
Dim Employees As Long
Dim e
Dim MyMonth

Sub CountSickPeriods()
Set StartCell = Range("D11")
Employees = 200

For MyMonth = 1 To 12
Sheets(MyMonth).Select ' Assuming "Summary" sheet is number 13
r = StartCell.Row
LastColumn = StartCell.Offset(-3, 0).End(xlToRight).Column
For e = 1 To Employees

For Col = StartCell.Column To LastColumn
Test = Cells(r, Col + 1).Value
If Cells(r, Col).Value = Sick And Cells(r, Col + 1).Value < "s"
Then
IsSick = IsSick + 1

End If
Next
Cells(r, 36) = IsSick
IsSick = 0
r = r + 2
Next
Next
End Sub

Regards,

Per