Google Sheets adding time to timesheet calculation

Question

I am trying to make a timesheet that calculates hours worked and include variable overtime hours, and subtract my unpaid half hour lunch.

I have start time and end time in individual cells, in 24 hour format 07:30 and 15:30 I can subtract these and get 08:00. that part is easy, but I need to subtract a daily 30 min time for unpaid lunch. so I have tried this (M9-L9)-0.0208333332 as a cell formula. it works and gives me 07:30 as result, but formatted as time, not decimal hours. Now I need to add to the result, from another column, any overtime in my day. That can range from 0.5 hours to 2.0 hours or more in a day. I would like that to add into the daily total hours worked column as well. and then be able to sum the total daily hours worked column, into a total monthly hours worked cell.

Kind of like this:

ShiftStart  Shiftend  Overtime  TotalHours
07:30       15:30     0.5       8
07:30       15:30     0.75      8.25
07:30       15:30     1         8.5
07:30       15:30     2         9.5
07:30       15:30     3         10.5
07:30       15:30               7.5

TotalWeek 52.25

It looks like it would be simpler to just format the first result, the Total hours, to decimal hours then do the maths on decimal results.... where/what is that format/conversion thingy?

Anyway, any help would be appreciated and thanks in advance for your assistance.

Try to look into these sql functions: TIMEDIFF();, SEC_TO_TIME();, HOUR();, MINUTE(); and SECOND();. You can use these and perform normal subtractions, additions, whatever you'd like to achieve your goal. — Martin

pnuts pnuts · Accepted Answer · 2018-06-01T18:17:18

Assuming ShiftStart is in A1 etc, I suggest you format your results as Number to 2 DP and apply in D2 and copied down to suit:

=24*(B2-A2)+C2-0.5

In this case summing D2:D7 returns 52.25 (Automatic Format).

Google Sheets adding time to timesheet calculation

1 Answers