How to calculate time difference

[DeepakS]

417:31:07 555:00:00 137:28:53
336:52:20 186:00:00 ########


=B1-A1, I tried this. But, in second row i am not getting the negative difference. Please help.

[zbor]

Try =MOD(B1-A1, 1)

[Andrew-R]

Or =ABS(B1-A1)

[DeepakS]

Hi zbor,

I am getting result like this,

417:31:07 555:00:00 17:28:53
336:52:20 186:00:00 17:07:40

[zbor]

Format cell as [h]:mm

[DeepakS]

Hi Andrew-R,

It works. But i need negative sign.

[Andrew-R]

OK... =IF(A1>B1,"-","") & TEXT(ABS(B1-A1),"hh:mm")

[DeepakS]

Now its like this,

417:31:07 555:00:00 17:28
336:52:20 186:00:00 -06:52

[Andrew-R]

D'Oh! Sorry, just me being stupid ... =IF(A1>B1,"-","") & TEXT(ABS(B1-A1),"[h]:mm")

[DeepakS]

:-) Its working perfectly. Thanks a lot Andrew-R.

[daddylonglegs]

Note that if you switch to 1904 date system you can use just =B1-A1 and you'll see negative time values

Office Button > Excel Options > Advanced > 1904 date system

Note that if you apply this to a worksheet existing dates in that sheet will change