0

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

1 Answer 1

2

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

image

5
  • I've tried applying your formula, but received #REF errors. However, rather than targeting a range, when targeting specific cells, it looks to work (i.e. =arrayformula(if(D5 > C5, D5-C5, D5+1-C5))). However, if you look at row 39, the hours are off massively. Why is this? The column has been formatted as duration also. I've updated my spreadsheet to simplify and demo
    – Freddy
    Mar 2, 2022 at 17:03
  • Is it the 1 day addition which is causing this? Row 39 should just be 0.21
    – Freddy
    Mar 2, 2022 at 17:06
  • As I said, you'll need to clear the column, before apply a calc with Arrayformula() docs.google.com/spreadsheets/d/…
    – Daniele
    Mar 2, 2022 at 19:40
  • for row 39... click on it... you'll see that the format is wrong...(31/12/1899 00:43:12)... I just changed it to 00:43:12
    – Daniele
    Mar 2, 2022 at 19:43
  • ah, good spot. Works now :) Thanks for the assist!
    – Freddy
    Mar 3, 2022 at 11:16

Your Answer

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

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