Problem subtracting times

[Zigi]

Hi all,

I am trying to deduct one time from another. Formatting in [h]:mm works fine EXCEPT when the result is zero, then it comes up as hashes with the message "dates and times that are negative or too large display as ######".

Any suggestions please?

Cheers
Zigi

[Peter_SSs]

If you don't need numerical time values for future calculations, try

=TEXT(MAX(A2:B2)-MIN(A2:B2),IF(A2<B2,"-","")&"[h]:mm:ss")


If you do need the times as numerical values then another option is to change Excel's default date system for this workbook to the 1904 date system and negative time should display correctly with a simple subtraction:

=A2-B2

[Zigi]

Thanks Peter_SSs - I ended up using the 1904 date

[Peter_SSs]

Cheers, glad it worked for you.

[JBeaucaire]

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.