Dates, Resources, Hours, Reg, OT...

[PaulF]

Hello ALL!

I have a challenge that is straining my brain

I have a work release form. Drop work to select a skill craft, start date, end date, resource count, hours per day, days per week, regular time rate, and over time rate.

Days per week: 4 = Mon - Thu | 5 = Mon - Fri | 6 = Mon - Sat | 7 = Mon - Sun

Weeks can start and end on any day.

The challenge is a 6 dimension puzzle in my brain and I hope I am just over thinking this.

I have been able to get the hours (without figuring out REG/OT) with this formula:

=IF(L26=4,INT((WEEKDAY(G26-2)-G26+I26)/7)+INT((WEEKDAY(G26-3)-G26+I26)/7)+INT((WEEKDAY(G26-4)-G26+I26)/7)+INT((WEEKDAY(G26-5)-G26+I26)/7),IF(L26=5,INT((WEEKDAY(G26-2)-G26+I26)/7)+INT((WEEKDAY(G26-3)-G26+I26)/7)+INT((WEEKDAY(G26-4)-G26+I26)/7)+INT((WEEKDAY(G26-5)-G26+I26)/7)+INT((WEEKDAY(G26-6)-G26+I26)/7),IF(L26=6,INT((WEEKDAY(G26-2)-G26+I26)/7)+INT((WEEKDAY(G26-3)-G26+I26)/7)+INT((WEEKDAY(G26-4)-G26+I26)/7)+INT((WEEKDAY(G26-5)-G26+I26)/7)+INT((WEEKDAY(G26-6)-G26+I26)/7)+INT((WEEKDAY(G26-7)-G26+I26)/7),IF(L26=7,INT((WEEKDAY(G26-2)-G26+I26)/7)+INT((WEEKDAY(G26-3)-G26+I26)/7)+INT((WEEKDAY(G26-4)-G26+I26)/7)+INT((WEEKDAY(G26-5)-G26+I26)/7)+INT((WEEKDAY(G26-6)-G26+I26)/7)+INT((WEEKDAY(G26-7)-G26+I26)/7)+INT((WEEKDAY(G26-8)-G26+I26)/7)))))

I am assuming I can shorten this *crossfingers*

It is essential 1 formula running checking the number of days selected and then summing the number if matching days in the start / end dates.

Is there a way to shorten this and then how to I calculate for Reg vs. OT hours. Yes I can use helper columns. OT always starts after 40, but calculating that for beginning and ending week is giving me the challenge in my brain.

Thank you in advance for any assistance.

Respectfully,
Paul F

 

[John Jairo V]

Hi @PaulF

You can short the above formula with this:

=SUMPRODUCT(INT((WEEKDAY(G26-ROW(A$1:INDEX($A:$A,L26))-1)-G26+I26)/7))*K26*M26

Blessings!

 

[PaulF]

Hi @PaulF

You can short the above formula with this:

=SUMPRODUCT(INT((WEEKDAY(G26-ROW(A$1:INDEX($A:$A,L26))-1)-G26+I26)/7))*K26*M26

Blessings!

John,

Thank you!!! Some days array and sumproduct formulas are obvious to me and other days... not so much :-(

Respectfully,
Paul F

 

[r2c2]

Good one @John Jairo V

Here is one more.

=SUMPRODUCT(--(WEEKDAY(ROW(OFFSET($A$1,G26-1,):OFFSET($A$1,I26-1,)),2)<=L26))*M26*K26

 

[PaulF]

The final results was a VBA solution:

Function GetHours(overtime As Boolean, stDate As Date, enDate As Date, dailyhours As Integer, workdays As Integer)
   
    Application.Volatile 'This makes the function behave like the builtin functions like =SUM()
   
    Dim d As Date
    Dim weekNum As Integer
    Dim startWeekNum As Integer
    Dim totalHours As Integer
    Dim totalOvertime As Integer
    Dim Hours As Integer
    Dim curWeekNum As Integer
    Dim CurWeekday As Integer
   
    ' get the starting week number
    startWeekNum = WeekNumberFromFirstDayOfWeek(stDate, 2)
   
    ' these are the counters
    totalHours = 0
    totalOvertime = 0
    curWeekNum = startWeekNum
    Hours = 0
   
    ' Now loop through the dates one by one, d being the date we are working on
    For d = stDate To enDate
        ' here we get the weekday number of d
        CurWeekday = Weekday(d, vbMonday)
        ' here we check to see if the weekday number is less than or equal to
        ' our defined work week and not a holiday
        If CurWeekday <= workdays And CheckForHoliday(d) = False Then
            ' now check if this is a new week of if we are still working on the old week
            weekNum = WeekNumberFromFirstDayOfWeek(d, 2)
            If curWeekNum <> weekNum Then
                ' its a new week, lets tally last week
                If Hours > 40 Then
                    totalOvertime = totalOvertime + (Hours - 40)
                    totalHours = totalHours + 40
                Else
                    totalHours = totalHours + Hours
                End If
                ' reset the couters for the new week
                curWeekNum = weekNum
                Hours = 0
            End If
            ' now lets continue with this week
            Hours = Hours + dailyhours
        End If
    Next
   
    ' now tally the last week if needed
    If Hours > 40 Then
        totalOvertime = totalOvertime + (Hours - 40)
        totalHours = totalHours + 40
    Else
        totalHours = totalHours + Hours
    End If

    ' return results - this allows us to use one function to get OT or Reg hours
    '  we could do this another way and have the function put the OT in the cell
    '  to the right of the calling cell, reducing the times this function is
    '  called in half.
    If overtime = True Then
        GetHours = totalOvertime
    Else
        GetHours = totalHours
    End If
   
End Function

Private Function CheckForHoliday(curDate As Date) As Boolean

Dim rng As Range

    CheckForHoliday = True
    Set rng = Worksheets("Constants").Range("HolidaysList").Find(curDate, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
        CheckForHoliday = False
    End If
End Function

Private Function WeekNumberFromFirstDayOfWeek(DT As Date, DayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromFirstDayOfWeek
' This returns the week number of the date DT based on Week 1 starting
' on the first DayOfWeek of the year of DT.
' Formula equivalent:
'      =TRUNC(((DT-DATE(YEAR(DT),1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YEAR(DT),1,1)),7))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WeekNumberFromFirstDayOfWeek = Int(((DT - DateSerial(Year(DT), 1, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(Year(DT), 1, 1)), 7)) + 6) / 7)
End Function

Private Function WSMod(Number As Double, Divisor As Double) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WSMod
' The Excel worksheet function MOD and the VBA Mod operator
' work differently and can return different results under
' certain circumstances. For continuity between the worksheet
' formulas and the VBA code, we use this WSMod function, which
' produces the same result as the Excel MOD worksheet function,
' rather than the VBA Mod operator.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WSMod = Number - Divisor * Int(Number / Divisor)
End Function