1

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.

Pieter-Jan Casteels
  • 93
  • 10

3 Answers3

1

Use Text format:

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

enter image description here

user11982798
  • 1,878
  • 1
  • 6
  • 8
0

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.

enter image description here

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.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • This works for hours alone. And i understand why you came to the assumtion i just needed hours. But i also need the minutes. – Pieter-Jan Casteels Jan 04 '20 at 15:22
  • I think my first custom format is equivalent to the time format described in your answer - so as far as I can see we are both right. – Tom Sharpe Jan 04 '20 at 16:10
0

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
Pieter-Jan Casteels
  • 93
  • 10