CODEWITHSUNDEEP
excelvbaexcel-formula
Tom
Tom

Reputation: 291

Formula to calculate overtime hours not working consistently

Edit: It now calculates overtime after implementing Vincent's solution in the comments

My girlfriend works from home 6 hours a day and gets to make her own hours. She asked me to make a time sheet for her that she can enter her start and end times into and will automatically calculate her hours. It calculates the total hours worked no problem (so long as they don't go pass midnight), but I wanted to also have it track overtime hours for her.

I have a formula that should work, but the problem is that if all the start and end times on a given day are both AM or both PM then it will not calculate the overtime hours no matter how much the total hours surpass 6 hours. However, as soon as you enter in a time value that starts at an AM time and ends at a PM time, such as 11:00 to 14:00, then it calculates the total overtime hours for the day just fine.

Here's a screenshot of the time sheet with times entered with total hours above 6, but no start and end time crossing from AM to PM:bad outcome

Here's an example where the the last time entered on the first day now goes from an AM time to a PM time. Notice that the overtime hours are now being calculated accurately. good outcome

The VBA generating the formulas that calculates the hours looks like this:

Sub calcHours(ByVal numDays As Integer)
    Dim newWeekRow As Integer: newWeekRow = 0

    With Sheets("timesheet")
        'Add the formula to calculate hours for the day
        Range(Cells(5, 17), Cells(numDays + 4, 17)).FormulaR1C1 = _
        "=SUM((RC[-2] - RC[-3]) + (RC[-4] - RC[-5]) + (RC[-6] - RC[-7]) + (RC[-8] - RC[-9]) + (RC[-10] - RC[-11]) + (RC[-12] - RC[-13]))"

        'Add formula to calculate overtime hours
        Range(Cells(5, 19), Cells(numDays + 4, 19)).FormulaR1C1 = _
        "=IF(RC[-2]-(6/24) > 6/24, RC[-2]-(6/24), 0)"

        'Add the formula to calculate hours for the week
        For ctr = 1 To numDays
            If (Cells(4 + ctr, 2).Value = "Saturday") Then 'found the end of the week
                If (newWeekRow = 0) Then 'end of the first week
                    Cells(4 + ctr, 18).FormulaR1C1 = "=SUM(R5C[-1]:RC[-1])"
                    newWeekRow = 4 + ctr
                Else
                    Cells(4 + ctr, 18).FormulaR1C1 = "=SUM(R[-6]C[-1]:RC[-1])"
                    newWeekRow = 4 + ctr
                End If
            End If

            If (ctr = numDays) Then 'reached the end of the last week
                Cells(4 + ctr, 18).FormulaR1C1 = "=SUM(R" & newWeekRow + 1 & "C[-1]:RC[-1])"
            End If
        Next ctr

        'Add formula to calulate total hours for the month
        With Cells(5 + numDays, 17)
            .FormulaR1C1 = "=SUM(R[-" & numDays & "]C:R[-1]C)"
            .Font.Bold = True
        End With
    End With
End Sub

Upvotes: 1

Views: 880

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

Currently your formula for suming the hours in the week is essentially:

=SUM(E5-D5,G5-F5,I5-H5,K5-J5,M5-L5, O5-N5)

The problem is that when you go past midnight you have no way of indicating that the time is the following day. Since Time is stored as a decimal and date is stored as the integer, and by default, when only time is entered, 0 is the integer for the day. All you need to do is add 1 to the time when they out time is less than the in time. Going to cheat a bit and assume there will never be more than a 24 hour period between in and out. So with that cheat in mind and that boolean True and False get converted to 1 and 0 respectively when going through a math operator, you could adjust your formula as follows:

=SUM(E5+(E5<D5)-D5,G5+(G5<F5)-F5,I5+(I5<H5)-H5,K5+(K5<J5)-J5,M5+(M5<L5)-L5, O5+(O5<N5)-N5)

Alternatively you could record the hours on the day they were worked on, not broken by the time you slept overnight as the delineater for the day. Ie if you work from 2300 on monday to 0100 on tuesday, then record monday as time in 2300 and time out as 23:59, and Tuesday record time in as 00:00 and time out as 01:01. Note the added 1 minute to the time out. This is to make up for the missing minute between 23:59 and 24:00 (which does not technically exist in excel but does work with some operations)

As for the overtime calculation see Vincent G's comment. Another spin on that formula could be:

=MAX(Q5-6/24,0)

POC

You will need to change the cell references to match your VBA programming needs. Both formulas correct overtime issue calculation and start and end time crossing midnight.

Upvotes: 1

GChuf
GChuf

Reputation: 2230

First, if you go over midnight, here's what I would do:

Going over midnight means the hours will be negative. In that case, do an IF statement where if the result is a negative number, simply add 24 hours (or 1 day) to the result.

Second, couldn't you calculate the overtime without VBA by simply subtracting 6 hours from total working hours for the day? I'm not too familiar with VBA, so if I'm missing something blatantly obvious, please forgive me.

Upvotes: 1

Related Questions