# How to calculate difference in hours between two time fields which runs from one day to the next

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.

## 3 Answers

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.

In addition to previous post, I believe this also works:

``````=MOD(F2-E2,1)
``````

and format the output column as 'duration.

or..

``````=value(F2-E2)
``````

with the output column formatted as 'duration'.

• Since the OP wrote "from one night to the next morning" it seems not very likely that the output will ever be more than 24 hours. Also I don't think dates are used at all, so the mod-function should do well in calculating overnight time. Anyway, I updated my answer with an alternative that should meet your requirement. Greetings, JP. – JPV Jun 13 '15 at 8:31

I can't speak to Google spreadsheets, but in Excel/LO you would set the cell format of the cells with a date in it to [number/general]. Do your math on the numbers.
The result will be in days, so then you can convert to whatever units you want.