Google Spreadsheet time between date - hour calculation

Question

I am at a loss, i looked around the internet and stackoverflow but every so called solution is giving either errors or plainly don't work.

I have the following setup. 4 fields (setup in date dd-mm-yyyy, hour hh:mm:ss) seconds are not important.

start date : 7-1-2020

start hour : 23:30:00

end date : 8-1-2020

end hour : 03:50:00

What i want to happen is to calculate the diffrence in 'hours, minutes' between the end and the start date, hour. But when I calculate and change the end date to lets say 09-01-2020 it does not count the extra 24h at all.

Answer 1

Use Text format:

=text(A3-A1+A4-A2,"[H]:MM")

Answer 2

After alot of fiddeling and this post i came to the conclusion that the main issue was not laying within the mathematical but within the format of the cell.

1. By default all time values in sheets are 24h max.
2. So the basic formula =start - end
3. The time format needed should be
   • more date time format
   • elapsed hours : elapsed minutes
   • apply
4. Now you should see the correct elapsed hours and minutes

Answer 3

You need to format the time difference as a duration using the custom format

[h]:mm

for hours and minutes

or

[h]

for whole hours.

There are some good notes on how it works in Excel here and as far as I can tell from testing it Google Sheets is the same.

Alternatively, if I read your question as wanting to drop the minutes and seconds from the times before doing the calculation, you could use

=(B3-B1)*24+hour(B4)-hour(B2)

and just format the result as a normal number.