Forum Discussion
Solved
How to count periods of absence in excel
Hey, I am creating a spreadsheet to track holidays and absence. I need to figure out a way of counting periods of absence. For example, in the attachment I want TEST 1 to show as one period of absenc...
What do you see when you open the sample workbook attached below?
Here is a custom VBA function that you can use:
Function Periods(daterange As Range, coderange As Range, code As String)
Dim i As Long
Dim f As Boolean
For i = 1 To daterange.Count
If Weekday(daterange(i).Value, vbMonday) <= 5 Then
If coderange(i).Value = code Then
If Not f Then
Periods = Periods + 1
f = True
End If
Else
f = False
End If
End If
Next i
End FunctionIn CC3, enter the formula
=Periods($B$9:$B$42,CC9:CC42,"S")Adjust the ranges as needed, then fill to the right.
Don't forget to save the workbook as a macro-enabled workbook and to allow macros when you open the workbook.
- Thanks for the VBA code, with this my formula does what I want. The issue I am having is that we have some weekend staff and I need their absences counted as well. Could you provide me with another code please?
Do weekend staff work only Saturday/Sunday, or do they work other days as well?
HansVogelaar there is a mixture. Some work Sat - Wed, some Sunday only and some weekend only. My file has very similar layout to the one shown in this thread. I can make some modifications if needed to help with the calculations. Maybe a code in one of the rows to identify staff who work on above days?
HansVogelaar Thank you, I'm one step closer now! That doesn't quite get what I'm after though, I need TEST 1 to show as one period and so on as over a weekend should still count as one period. It should only count as more if there is a working day between periods.
What do you see when you open the sample workbook attached below?
Hi Hans
Am picking up an old thread you have helped with.
I have used your period function for my calculation but as we are using several reasons for absenteism the outcome is current double counting some periods.
For example i could have an employee being sick for 5 days and this will be recorded as 3 days of sick holiday (SH) meaning that he will use his holiday allocation for 3 days and 2 days as sick (S) which would be unpaid.
So the formula below would need to be able to look at different reason code
=Periods(last_year_dates,OFFSET(last_year_dates,12,0),"S")
Do you have any suggestion on how i could achieve this?
Thankyou very much.
