Dec 09 2021 07:33 AM
Dec 09 2021 07:33 AM
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.
Dec 09 2021 08:07 AM
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
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.
Dec 10 2021 02:26 AM
@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.
Dec 10 2021 03:57 AM
Mar 03 2023 12:57 PM
Mar 03 2023 01:26 PM
@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?
Mar 03 2023 02:01 PM
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.
Nov 09 2023 03:42 AM
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
Do you have any suggestion on how i could achieve this?
Thankyou very much.
Nov 13 2023 05:35 AM
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
Nov 13 2023 07:32 AM
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?