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 absence whereas TEST 2 should have two and TEST 3 should have three. 11 Replies

# Re: How to count periods of absence in excel

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 Function``````

In 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.

# Re: How to count periods of absence in excel

@Hans Vogelaar 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. best response confirmed by Jack_Roberts (New Contributor)
Solution

# Re: How to count periods of absence in excel

What do you see when you open the sample workbook attached below?

# Re: How to count periods of absence in excel

Hey, that's what I am after thank you. I have copied that formula into my sheet and it works as desired now

# Re: How to count periods of absence in excel

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?

# Re: How to count periods of absence in excel

Do weekend staff work only Saturday/Sunday, or do they work other days as well?

# Re: How to count periods of absence in excel

@Hans Vogelaar 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?

# Re: How to count periods of absence in excel

See the attached workbook. You can now optionally specify a workdays string.

The workdays string must be seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a workday and 0 represents a non-workday.

For example, "0000011" means that only Saturday and Sunday are workdays.

If you omit the workdays string, "1111100" is assumed, i.e. workdays are Monday to Friday.

# Re: How to count periods of absence in excel

@Hans Vogelaar this is genius, thank you. How can I make the formula not to be case sensitive?

# Re: How to count periods of absence in excel

Change the line

``                If coderange(i).Value = code Then``

to

``                If UCase(coderange(i).Value) = UCase(code) Then``

# Re: How to count periods of absence in excel

Thank you so much