SOLVED

How to count periods of absence in excel

New Contributor

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.

 

Please help!

 

Jack_Roberts_1-1639063923255.png

 

 

4 Replies

@Jack_Roberts 

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.

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

Jack_Roberts_0-1639131648462.png

 

best response confirmed by Jack_Roberts (New Contributor)
Solution

@Jack_Roberts 

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

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