Displaying negative time in Excel 2013

[T_S_O_P]

Hi Mr Excel team

I created a time sheet last year and with the help of the friendly staff here was able to come up with a formula that showed time owing in red, over time in green and to time, i.e. 0 hours as black with the following formula:

=IF(U5=0,"",U5-$H$1/39)

H1 is the staff members contract dived by the length of weeks in the year it is worked. U5 is the total hours worked that week.

The cell in which the formula is places is formatted is custom +h:mm;-h:mm;0

It worked in Office 2007

It doesn't work in Office 2013

Minus hours and zero hours are displaying as a continuous line of ########################, in red.

T_S_O_P

 

[shg]

You would need to change to the 1904 date system in Options. Be aware that that will change any manually-entered date to 462 days later.

 

[Andrew Poulsom]

To have negative times you need to use the 1904 date system.