Forum Discussion
Format cell for displaying negative result when summing time format
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?
4 Replies
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.
- 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 - click File tab, and select Options from the menu list. And the Excel Options dialog will open.
- 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
