How to count periods of absence in excel

[Jack_Roberts]

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!

[#2]

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

[#3]

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

[#4 · Accepted solution]

@Jack_Roberts 

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

[#5]

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

[#6]

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?

[#7]

@Dagmara912 

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

[#8]

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

[#9]

@Dagmara912 

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.

[#10]

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

[#11]

@Dagmara912 

Change the line

                If coderange(i).Value = code Then

to

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

[#12]

Thank you so much

[#13]

@HansVogelaar 

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.

[#14]

@Olivier_Languille 

Do you mean that the function should count the 3 SH days and 2 S days as one period?

[#15]

@HansVogelaar 

Yes this is what I mean because we record it in two different way but this is the same period.

Day 1	Day 2	Day 3	Day 4	Day 5
SH	SH	SH	S	S

Again, this could also be over weekends

Hope it clarifies.

[#16]

@Olivier_Languille 

Thanks. Is S/SH the only combination that should be treated this way, or are there others - if so, please list them in a reply.

[#17]

Hans
the combination can be S, SH, U,BH

Thank you

[#18]

@Olivier_Languille 

See the attached version.

[#19]

@HansVogelaar 

Thanks for your file.

The period count is not what i would have expected. In column G i would have expected to be 2 periods, column H is correct, column I should be 4, column J should be 5, etc....

Not sure if i am missing something.

I have attached a snap shot of my file. Again the period count seems to be incorrect. For example line 20 does not have any of the S or SH or U but it is still counting 8 periods whilst i would expect it to be 0.

I have remove the BH value as i do not need it to be counted

Thanks

[#20]

@Olivier_Languille 

Apparently, you want something else than @Jack_Roberts who started this discussion. They wanted to count periods as if weekend days didn't exist. For example, if an employee is sick on:

Thu 9 Nov, Fri 10 Nov, Mon 13 Nov and Tue 14 Nov, it should count as 1 period since Sat 11 Nov and Sun 12 Nov should be ignored.

Can you explain what you want?