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

google sheets

I've found a formula that will calculate how many hours between 2 time perfectly well if they are in the same day, but as soon as the times go from one night to the next morning I end up with a minus number of hours.

Here is the formula:

=iferror(if((minute(F2) - minute(E2))>=0,hour(F2)-hour(E2) + (minute(F2) - minute(E2))/60,hour(F21)-hour(E2) + 1 + (60 - minute(F2) - minute(E2))/60))

For example, if I work from 10 pm till 11:30 pm it gives me answer of 1.5hrs, but if I input 11 pm till 2 am it comes back with an answer of –22.

Best Answer

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.

Related Topic