Subtotal SUM does not work properly when more than 24 hours

Question

I have the following data in column H:

=SUBTOTAL(109;H3:H9999)
hours
02:15
03:30
03:30
00:15
00:15
02:00
00:30
00:30
04:00
00:30
05:45
02:15
00:30
02:00
01:00

I want to have the subtotal with function 109 (SUM ignoring hidden / filtered out rows) at the top in row 1. However the result is: 4:45 which is not correct obviously. Any suggestions?

I already tried to format the cell using "[h]:mm" but this did not work either.

Furthermore I tried https://stackoverflow.com/a/58549941/1439628 but this only works for SUM and does not seem to work for SUBTOTAL.

Answer 1

Try formatting the cells as duration and not as time, since the time formatting laps around after 24h.

Answer 2

Found the error. Maybe I should have added column F and G too. These contain start time and end time. The data from column H that I provided above is calculated as difference. Now, there was one line where start time is before and end time is after midnight:

23:30 01:30 02:00

If I enter 25:30 instead of 01:30 everything works correctly. And it is displayed as 01:30 nevertheless.