# Sum hours worked depending on day type

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 weekend days, I should add it to total; or
• If the day is a holiday and I'm looking for weekend days, 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 weekday days, 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

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
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 Just to build off RubberDuck's answer -

=SUMPRODUCT((H6:H10)*((WEEKDAY(A6:A10)<6)*(A6:A10<>DATEVALUE("1/1/2017"))))


You could add a cell with the holidays and put that range in the DATEVALUE. This will give you weekdays. To get the other days, just sum H6:H10 (for example) and subtract the above formula.

If you want to specify the length of days, you'll need to work in an INDIRECT

• Hmm, I'm not very familiar with this formula. I have a worksheet Feriados with my holidays ranging from A3:A17. I tried to use your formula as =SUMPRODUCT((H6:H10)*((WEEKDAY(A6:A10)<6)*(A6:A10<>DATEVALUE(Feriados!A3:A17)))) but it didn't work. How could I replace the Datevalue with the range of holidays in that formula? – Victor Moraes Jan 26 '17 at 16:31
• something like ((ISERROR(MATCH(A6:A10,Feriados!A3:A17),0)))* included in the sumproduct – Raystafarian Jan 26 '17 at 17:03
• Thank you very much! Now I just need to find a way to dynamically get my range to work with. After that I believe I'll be able to completely replace my code with it. – Victor Moraes Jan 26 '17 at 18:14
• Good to hear. Try using indirect, but you might need a helper column, I can't get sumproduct to use the indirects I make – Raystafarian Jan 26 '17 at 18:17
• Got it to work with =$H$6:INDEX($H:$H,MATCH(9.99E+307,$H:$H)), but to make my life easier I've created a named range Totals_Day with =Lançamentos!$H$6:INDEX(Lançamentos!$H:$H,MATCH(9.99E+307,Lançamentos!$H:$H)) and also created named ranges for Worked_Days and Holidays. Now my formula is =SUM(Totals_Day)-SUMPRODUCT((Totals_Day)*((WEEKDAY(Worked_Days,2)<6)*((ISERROR(MATCH(Worked_Days,Holidays,0)))))) to get total of hours worked in holidays and weekends. Thanks again – Victor Moraes Jan 26 '17 at 18:44

Don't use VBA when an Excel formula can do the job. Look into the Workday function. It takes in a date and an optional array of holidays and returns a number indicating the day of the week. I have a feeling with just a bit of effort, it can completely replace your VBA code.

• I'm sorry, but can you elaborate on how the Workday function could replace my code? It requires a start day (ok, I have that) and a number of days related to the starting day (don't know what I could provide here), and then will return a number of working days related to the start date provided (different than "the day of the week"). How could I use that information to sum the number of hours worked in a weekend, in a holiday or in a working day? I believe you meant to say it could be part of a big formula, but I still can't figure out how to apply it to my context. Thanks in advance – Victor Moraes Jan 26 '17 at 13:48