I have a spreadsheet where I control my shift hours. In such spreadsheet I input the date, the start time, time left for lunch, time back to work, end time and sometimes the overtime.
After that I have created a couple of functions to calculate the amount of time worked, the difference against the 8-hour shift expected time, bank of hours and so forth.
Everything is working fine, but I'm not very happy with the performance of it and I suspect the reason is one of my functions, which checks whether the current date is a holiday or not.
This is my current function:
Function IsHoliday(cell As Date) As Boolean Dim item As Range Dim holidayList As Range ' On Error Resume Next Set holidayList = Sheet2.Range("A3:A17") For Each item In holidayList If cell = item.Value Then IsHoliday = True Exit Function End If Next On Error GoTo 0 IsHoliday = False End Function
Sheet2 from the code is related to this spreadsheet:
The function is called in for each row in my Main Sheet, as highlighted in the picture below:
There are probably more issues in this, but I think the main issue is that I'm using a
For Each loop to check if the date is a holiday in each row, but I sincerely don't know a faster way to do it. Can anyone share their thoughts on improvement points?
If you feel the need to check other functions and/or calculations in order to help me, just let me know.