4
\$\begingroup\$

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

shiftHoursControl

\$\endgroup\$

2 Answers 2

3
\$\begingroup\$

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

\$\endgroup\$
6
  • \$\begingroup\$ 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? \$\endgroup\$ Jan 26, 2017 at 16:31
  • \$\begingroup\$ something like ((ISERROR(MATCH(A6:A10,Feriados!A3:A17),0)))* included in the sumproduct \$\endgroup\$ Jan 26, 2017 at 17:03
  • \$\begingroup\$ 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. \$\endgroup\$ Jan 26, 2017 at 18:14
  • \$\begingroup\$ Good to hear. Try using indirect, but you might need a helper column, I can't get sumproduct to use the indirects I make \$\endgroup\$ Jan 26, 2017 at 18:17
  • 3
    \$\begingroup\$ 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 \$\endgroup\$ Jan 26, 2017 at 18:44
3
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ 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 \$\endgroup\$ Jan 26, 2017 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.