3

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.

5

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.

2

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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.