Formula help nesting 3 if statements

[Stryker2013]

I have a spread sheet which tracks weekly logged work hours. I total the days to a weekly total. I want that total to compare to a 40 hr week and be reflected in a billed column. I have three if statements I need to work. 1st if is the 40 hr worked is equal to my standard week of 40 hrs the displays a 0. The standard 40 hrs is in Cell H1 and formatted as 40:00. The second if statement is to compare the week and if it is more than 40 hrs display the overage as a positive number. The 3rd if statement is to compare the week and if it is less than 40 hr display the number as a negative number. the formula I have right now partially works for the 1st two if's but the negative displayed number is not working. ALl i get is a long string of ###########.

1^{st attempt i have 40 in weekly total and a 40 in H1 the formula works in displaying a 0:00}
=IF(E7>$H$1,(E7-$H$1),IF(E7<$<wbr>H$1,($H$1-H7),0))

2^{nd attempt i modified 40:30 in the weekly total and 40 in H1 the formula works in displaying 0:30}
=IF(E14>$H$1,(E14-$H$1),IF(<wbr>E14<$H$1,($H$1-E14),0))

3^{rd attempt fails when I modified it to 36:30 in the weekly total and 40 hr in H1 all I get is #########}
=IF(E21>$H$1,(E21-$H$1),IF(<wbr>E21<$H$1,(-1*($H$1-E21)),IF(<wbr>E21=H1,0)))


Any ideas?

 

[The_Macrotect]

Hey Stryker,

It's a formatting issue. Excel displays negative dates and times as ########. My advice would be to change the format to "General" or "Number" and use "40" instead of "40:00". Your formula would only need to be this:

=E7-$H$1

 

[AlphaFrog]

Excel does support negative time values if you change the workbook setting to use the 1904 date system. If you do that, you could just use the formula...
=E7-$H$1

This article explains:
Differences between the 1900 and the 1904 date system in Excel

 

[Stryker2013]

Good Morning. Perfect this worked. Changing to 1904 allows the display to be a negative number. Thank you so much