Running balance into minus figures

Evening everyone.
Firstly I just want to say - fantastic forum, I have found many answers here that have helped me along the years, but now I need some more specific help.

So I am creating a running balance on a sheet in 2003 using hh:mm custom format. What I want is the cells to calculate a balance into minus figures. The positive figures show as green, the negatives as red, I used conditional formatting for this. But when the balance requires a negative figure the display is ############

Getting a "-" to display in excel is always a bit of a pain for me, I just enter a ' before and this normally works. I had to use that technique to get the conditional formatting to work properly in this case. I was thinking of using a custom format along the lines of hh:mm;[green]0.00;[red]-0.00; but I have had a play around with that and no luck.

This has really got me stuck and any help would be greatly appreciated as always. I have attached a draft of the workbook I am using if it helps.

Excel won't display negative times unless you use the 1904 date system, which is a bad idea.

You can format negative times as, e.g., -0.0000

As I said, you can change to the 1904 date system.

That shifts the dates in every workbook by four years, though.

For anyone who opens the workbook without 1904 date settings, they're going to see some wacky dates. And you'll see wacky dates in everone else's.

Have at it, but I repeat, it's not a good idea.

Totally agree with shg's sentiments - switching Date Systems for aesthetics is not a great move.

Time in XL is Decimal so on the 1900 Date System you can have negative time values you just can't display them in Time Format as already outlined.

You can dummy around it by storing values as Text strings, eg - using your file:

F6:
=IF($C6="","",REPT("-",E6>($G$5-SUM($E5:$E$6)))&TEXT(ABS($G$5-SUM($E$6:$E6)),"hh:mm"))
copied down to F17