Finding time difference between two times (have time duration and SUM)

Question

I have a simple timesheet devised in Google sheets and simply want to calculate how many hours and minutes have elapsed since the start time, so I can then calculate how any total hours oof overtime have occured.

I have tried two approaches:

1. Formatted the "time started" and "time end" columns as "time" (format > number > time) and the "hours" column is formatted as "automatic" in my first example. Using SUM(), I've tried to get the total number of hours, but as you can see from row 49 in my demo below, the hours are incorrect for whatever reason.
2. I've then tried to use "duration" instead of "automatic" for my "hours" column, but it provides minus numbers, which when summed, will not provide the correct hours.

See demo in Google sheets here

Answer 1

There are 3 cases where "time end" is less than "time start", so you need to add 1 day (24 hours) to the difference. Using this calculation in J4 and formatting column J as "duration" you will solve (FIRST delete column J from J4 to J48):

=arrayformula(if(I4:I48 > H4:H48, I4:I48-H4:H48, I4:I48+1-H4:H48))