Google-sheets – How to calculate time worked in Google Sheets after 12am

google sheetsgoogle-sheets-cell-format

I usually work from 3pm to 1am. I can't seem to find out how to make google sheets find the correct duration between those times. I know the correct duration is 10 hours, but no matter what I do google sheets comes up with either 16hours and 14 hours.

Best Answer

try to format it as Automatic, Time or Custom Time instead of Duration

or you can format it as Duration if you add date 31/12/1899 before your time

Solution

Suppose total distance is in column C1 and total time is in D1 then average speed formula would be:

=C1/(int(D1)*24+hour(D1)+minute(D1)/60+second(D1)/3600)

int function is the one I was after.

Google-sheets – How to calculate difference in hours between two time fields which runs from one day to the next

As user3169 wrote, one can simply take the difference of time values (this works in Google Spreadsheets the same as in Excel). Specifically, the command

=ROUND((F2-E2)*24)

calculates the number of hours between the time values in the cells E2 and F2, rounded to the nearest whole hour. Similarly,

=ROUND((F2-E2)*24*60)

calculates the number of minutes between the time values in the cells E2 and F2, rounded to the nearest whole minute. Etc.

Best Answer

Related Solutions

Google-sheets – Parsing Time data in Google Spreadsheets

Solution

Google-sheets – How to calculate difference in hours between two time fields which runs from one day to the next

Related Topic