Forum Discussion
How can I display negative values for time in calculation results
To get time differences to display as hours and minutes (as opposed to a decimal part of a day), the value must be recorded as positive, even it that means using a formula in the form
= ABS(end - start)That doesn't prevent you from formatting the number to show as negative (with a minus sign or even with the accountancy trick of a red value within parentheses). A formula-based conditional format using the condition
= start > endcould be used to apply the number format
[Red]([h]:mm)The catch is that, like the text solution, the value in the cell cannot be used directly in dependent formula without correcting the sign.
There is another different trick used to express fractions by showing the numerator after the decimal place, with the denominator as given within the function. So
= DOLLARFR(24*(end - start), 60)
will give
-1.45for a negative time difference of 1 hour 45 minutes.