Excel Time Forumula

Question

I'm trying to find the difference between in/out times on a timesheet to calculate hours worked.

I have the following data:

----A----------B-----------C----------D-------
----Time In----Time Out----Time In----Time Out
1---11:15 AM---12:05 PM----1:05 PM----1:10 PM
2---1:05 PM----1:20 PM-----2:20 PM----4:00 PM
3---11:35 PM---12:05 AM----1:05 AM----1:30 AM
4---1:20 PM----2:20 PM------------------------

These cells are all formatted as Time (HH:MM AM/PM)

I'm using the following formula to calculate hours worked:

=(SUM(B1-A1)*24)+(SUM(D1-C1)*24)

This formula produces the following results:

----A----------B-----------C----------D-----------E----------
----Time In----Time Out----Time In----Time Out----TOTAL HOURS
1---11:15 AM---12:05 PM----1:05 PM----1:10 PM-----0.92-------
2---1:05 PM----1:20 PM-----2:20 PM----4:00 PM-----1.92-------
3---11:35 PM---12:05 AM----1:05 AM----1:30 AM-----(23.08)----
4---1:20 PM----2:20 PM----------------------------1.00-------

Rows 1, 2, & 4 are displaying properly, but Row 3 is displayed wrong. It should be .92 NOT -23.08. I know this is happening because the formula can't tell that column B (12:05 AM) is on the following day.

Unforunately, adding the date is not an option... time only. Can a formula be written to work around this limitation?

Answer 1

I am adding a VBA answer for those who might want to know as this is a good question and something other people may run into. VBA is more flexible in that you can have differnet formats for the time and it'll still work (for example you can have actual dates included in the time). This utilizes the solution from duffymo.

Function HoursWorked(t1 As Date, t2 As Date, _
                     t3 As Date, t4 As Date) As Double

Application.ScreenUpdating = False
Dim shift1 As Double
Dim shift2 As Double

shift1 = DateDiff("n", t1, t2) / 60
If shift1 < 0 Then
    shift1 = shift1 + 24
End If

shift2 = DateDiff("n", t3, t4) / 60
If shift2 < 0 Then
    shift2 = shift2 + 24
End If

HoursWorked = shift1 + shift2
Application.ScreenUpdating = True

End Function

Note that if shift 3 and 4 are blank the function will still run normally. Also note that I used seconds then divided by 60 to ensure precision is not lost (which it would be if you "h" instead of "n" in the DateDiff function).

And if you just want to get the number of hours between 2 dates/times, you can use this:

Function HoursDiff(ByVal date1 As Date, _
                   ByVal date2 As Date) As Double

Application.ScreenUpdating = False
Dim result As Double

result = DateDiff("n", date1, date2) / 60
If result < 0 Then
    result = result + 24
End If

HoursDiff = result
Application.ScreenUpdating = True

End Function

Answer 2

Take the result number modulo 24, it should give you a positive number all the time.

Your equation becomes:

=MOD(SUM((B1-A1)*24),24) + MOD((SUM(D1-C1)*24),24) 

Answer 3

If the result is less than zero, add 24.