A while ago I posted this question about finding the Date in Range, where I explained I have a spreadsheet to control my shift hours and was interesting in improving one of my functions.
I got really nice feedbacks from the community and the improvements provided really helped me out, but now I'm interesting in another function of mine (the caller of that previous function), so here is how this function works:
The function is intended to Sum the amount of hours worked from my spreadsheet, depending on the dayType, which can be either a weekend or a weekday. It also checks whether or not the day is a holiday (by calling the function from the aforementioned post) and finally it only considers days that are inside my monthInterval, which means I can determine if I want to get the total of hours worked from the past month, the past 2 month and so forth (defaulted to 3 months).
There are probably more ways to improve my function, so feel free to suggest any other improvements, but my main concern right now is about my If logic. I'm checking too many conditions in the same statement, but I couldn't think of a better way to do it. The logic is:
- If the day is a Weekend and I'm looking for
weekenddays, I should add it to total; or - If the day is a holiday and I'm looking for
weekenddays, I should also sum it (so I'm considering both holidays and weekends in the same result, because they don't enter in the Bank of hours); or - If the day is a regular day and I'm looking for
weekdaydays, I should sum it; and - I should only sum it if the day is inside the allowed range determined by
monthInterval.
Here is my current code:
Public Function SumHoursByDayType(ByVal firstCell As Range, _
ByVal dayType As String, _
Optional ByVal monthInterval As Long = 3) As Double
With ThisWorkbook.Worksheets(1)
Dim lastRow As Long
lastRow = .Cells(.Rows.count, firstCell.Column).End(xlUp).Row
Dim cellsList As Range
Set cellsList = firstCell.Resize(lastRow - firstCell.Row + 1, 1)
Dim cellsToSum As Variant
cellsToSum = cellsList.Value2
Dim datesList As Range
Set datesList = .Range("A" & firstCell.Row).Resize(lastRow - firstCell.Row + 1, 1)
Dim datesToCheck As Variant
datesToCheck = datesList.Value
End With
Dim index As Long
For index = LBound(cellsToSum) To UBound(cellsToSum)
If cellsToSum(index, 1) = "" Then Exit For
Dim currentDate As Date
currentDate = datesToCheck(index, 1)
Dim dateIsHoliday As Boolean
dateIsHoliday = IsHoliday(currentDate)
If ((Weekday(currentDate, vbMonday) > 5 And dayType = "weekend") _
Or (dayType = "weekend" And dateIsHoliday) _
Or (Weekday(currentDate, vbMonday) < 6 And dayType = "weekday" And Not dateIsHoliday)) _
And (DateDiff("m", currentDate, Date) <= monthInterval) Then
Dim total As Double
total = total + cellsToSum(index, 1)
End If
Next
SumHoursByDayType = total
End Function
Below is the IsDate function, from the previous post:
Private Function IsHoliday(ByVal givenDate As Date, _
Optional ByVal forceReload As Boolean = False) As Boolean
'--- establish a static dictionary to populate once, then
' reuse with each call
Const DATE_FORMAT As String = "mm/dd/yyyy"
Static holidays As Scripting.Dictionary
If (holidays Is Nothing) Or forceReload Then
Dim holidaysWS As Worksheet
Set holidaysWS = ThisWorkbook.Worksheets("Feriados")
Const FIRST_ROW As Long = 3
Dim lastRow As Long
Dim holidayList As Range
lastRow = holidaysWS.Cells(holidaysWS.Rows.count, "A").End(xlUp).Row
Set holidayList = holidaysWS.Range("A" & FIRST_ROW).Resize(lastRow - FIRST_ROW, 1)
Set holidays = New Scripting.Dictionary
Dim index As Long
For index = 1 To holidayList.count - 1
'--- the date (as a string) is the Key,
' the description is the Item
holidays.Add Format(holidayList.Cells(index, 1), DATE_FORMAT), _
holidayList.Cells(index, 3)
Next index
End If
IsHoliday = False
If holidays.Exists(Format(givenDate, DATE_FORMAT)) Then
IsHoliday = True
End If
End Function
In case you are interested, the code is currently only being called from cell F3, but I was previously also calling it from cell C3
