How to display negative time in days hh:mm format

[BlissC]

I have an Excel leave card which I'm having problems with displaying negative time in the days hh:mm format.

I've switched the workbook to the 1904 time format, so cells where I need to display time in hh:mm format where someone's exceeded their leave allowance are displaying just fine, displaying the '-' sign, but I can't figure out how to display it in the days hh:mm format. It's further complicated (at least it is with my level of understanding of Excel!) as the formula that produces the days hh:mm display is quite a complex one to prevent rounding errors that dogged earlier versions of the form.

On the 'Leave' sheet, column K (K30:K72) each cell subtracts the amount of leave requested from the previous remaining time. Switching to the 1904 date format correctly shows the time in hh:mm as a negative number, but if it's a negative number, column L (L30:L72) simply shows 'ERROR', as does cell P23. I want these cells to show the number of days, hours and minutes by which the employee has exceeded their leave allowance, for example:

Please Login or Register  to view this content.

The attached file is a working example of the form with some test data in it.

[XLent]

Given 1904 in place, you could try replacing both instances of 1/86400 with SIGN($K42)/86400
(and apply revised formula to the other rows)

[CK76]

Try...

Please Login or Register  to view this content.

Though, I'd recommend avoiding use of 1904 date system. But have calculation staging sheet to perform necessary calculations using underlying datetime value.
Sheet1 for data entry and display only (i.e. pull calculation result from another sheet).

[BlissC]

Thank you. That works perfectly.