Formula Discrepancy

[watermt]

I have a problem with one of my answers to a formula (see formulas below)
that I use throughout one of my worksheets. The error is the ########
message.

I've tried to comb through the formula and find the problem but cannot.
below is a copy of three records in my worksheet. Two of them return an
answer in [hh]:mm format as I anticipate, but one does not even though the
formulas are identical with the exception of the Cell reference?

Can anyone offer a solution?

Cell D111 Cell F111
1/24/2010 12:15:00 AM 1/25/2010 1:30:00 AM

=IF(OR(D111="",F111=""),0,(NETWORKDAYS(D111,F111)-1)*("15:00"-"06:30")+MOD(F111,1)-MOD(D111,1))

Cell N111
Answer: 01:15

*****************************************

Cell D139 Cell F139
11/12/2009 12:22:00 PM 11/12/2009 12:36:00 PM

=IF(OR(D139="",F139=""),0,(NETWORKDAYS(D139,F139)-1)*("15:00"-"06:30")+MOD(F139,1)-MOD(D139,1))

Cell N139
Answer: 00:14
******************************************


Cell D153 Cell F153
1/9/2010 3:10:00 AM 1/9/2010 4:00:00 AM

=IF(OR(D153="",F153=""),0,(NETWORKDAYS(D153,F153)-1)*("15:00"-"06:30")+MOD(F153,1)-MOD(D153,1))

Cell N153
Answer: #############

 

[Glenn]

Format the result as General and you will see that the result is negative.
Excel doesn't like negative times.

 

[Steve Dunn]

Taking 1 away from the working days when they are 0 (1/9/2010 is a Saturday)
results in a negative time, that's why you are getting the ######## result.

Try this:

=IF(OR(D15="",F15=""),0,(NETWORKDAYS(D15,F15)-
(NETWORKDAYS(D15,F15)>0))*("15:00"-"06:30")+MOD(F15-D15,1))

which will only take 1 away when working days are greater than 0.

HTH
Steve D.