Displaying negative time in Excel 2013

T_S_O_P

New Member
Joined
Oct 15, 2013
Messages
3
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,054
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top