I'm Having A Problem With Time Formula

[Drip_Drip]

I'm using the formula shown below in cell K5. If I drag the formula down it works correctly except cell K6 which shows 23:15 rather than 00:45. Can anyone assist so that the correct values are shown?

Please note that the values are based on hours and minutes which are formatted as [hh]:mm

Ideally I would like to show a reduction with a minus sign but this is not essential as I can use some conditional formatting to get around this.

Column E Column H Column K (Result)

842:31 832:25 10:06
501:51 502:36 23:15 (Incorrect)
832:25 502:36 329:49
502:36 501:51 00:45

Thanks in advance.

=IF(E5<H5,E5-H5+(E5<H5),E5-H5)

 

[Scott Huish]

The easiest way would be to use the 1904 date system, but this will affect any dates you currently have on the sheet, then
=H1-E1

if you want to use Conditional Formatting to show the negatives, then perhaps:
=ABS(H1-E1)

and a conditional formatting formula of:
=$E1>$H1

 

[barry houdini]

That sort of formula is used for calculating time differences when you have times between 00:00 and 24:00, it won't work here.

Excel can show negative times if you switch to 1904 date system

Tools > Options > Calculation > 1904 date system

but this will change any dates you have in your spreadsheet currently.

Using that you can just use

=E5-H5

or without changing date systems you could try

=IF(E5< H5,TEXT(H5-E5,"-[h]:mm"),E5-H5)

Note: that the negative values will be text strings that you can't easily use in other calculations