Format cell for displaying negative result when summing time format

[Birgir_F]

Version:

Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20292) 64-bit

Summing / subtracting time format

"[tt]: mm;@" does not display negative results, how shall it be for displaying negative results?

[#2]

@Birgir_F 

1. click File tab, and select Options from the menu list. And the Excel Options dialog will open.
2. click Advanced from the left pane, and check Use 1904 data system under When calculating this workbook section. Click Ok button

Another option is to make-up it by using TEXT() and MAX() and MIN() formulas if you want it  time formatted (HH:MM)

=TEXT((MAX(Q35:Q36)-(MIN(Q35:Q36)+K37)),CHOOSE(N(Q36<Q35)+1,"-[H]::MM","[H]::MM"))

Find attachment

[#3]

Hi,

there is only one way to work with negative times in Excel, which is to turn on the "Use 1904 date system" in the Excel options, advanced section. But this is something, I would not recommend (all dates are shifted by 4 years!)

Instead I propose to convert the times into decimal numbers by multiplying the time value with 24.

Kind regards,
Martin

[#4]

@Birgir_F 

Excel cannot handle negative times (except by changing a setting, but that would mess up all dates in your workbook). A workaround is to display the total times as decimal hours instead of as time. See the attached version.

[#5]

@Birgir_F 

as a variant: