I am creating a timesheet for our employees using google sheet.
Main purpose of the sheet:
Track daily hours and minutes (ex 7hours and 42mins)
Track weekly hours (overall hours for the week including minutes)
I already completed the said above requirements however, we are encountering errors when the person is logging 11:00PM – 7:00AM shifts.
Formula i use in getting hours and minutes:
=HOUR(B2-A2) and =MINUTE(B2-A2) – This is two separate cells
3
Get hours and minutes
To be able to get the exact hours and minutes between a set of times (time-in and time-out), you can use the formulas below:
Formula to get Hours:
=HOUR(IF(B2<A2, (B2-A2)+1, B2-A2))
Formula to get Minutes:
=MINUTE(IF(B2<A2, (B2-A2)+1, B2-A2))
These formulas effectively handle shifts that cross midnight by adding 1 day (24 hours) if the end time is before the start time. After putting in these formulas on the cells where you want to see the Hours and Minutes, you can just click the bottom right corner of the cell so that the formula will also take effect on the other rows that has Time-Ins and Time-Outs entries.
Sample Output
Note that these formulas are supposed to be on separate cells, just like your original setup. Also, make sure that you use proper formats, e.g., cells that contain time-ins and time-outs should be in time format, and the new cells (hours and minutes) should be in plain text.
References: Google Sheet’s IF function
1