Working hours Formula _ Please Help

[Moony]

Hi everyone
I have an error in my Excel formula in some cases but not the others (same
formula) and I just can't figure out the reason.
PLEASE help me.

Here is an example where I have the error message.

The formula:
=ABS(IF(INT(O543)=INT(P543),24*(P543-O543),(24*(DayEnd-DayStart)*(MAX
(NETWORKDAYS(O543+1,P543-1),0)+INT(24*(((P543-INT(P543))-(O543-INT(O543)))+
(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+MOD(((24*(P543-INT(P543)))-
24*DayStart)+(24*DayEnd-(24*(O543-INT(O543)))),(24*(DayEnd-DayStart))))))

gives #REF! as a result.

O543 is =F543-TIME(2,0,0) and F543 is 12/9/2009 10:52:00 AM
P543 is =H543-TIME(2,0,0) and H543 is 12/10/2009 10:41:33 AM

Can you see what the issue is?
Please help, many many thanks in advance

[Moony]

[Moony]

[Moony]

[FloMM2]

Moony,
I have to track equipment run time on a spreadsheet, which is similar to
what you have.
It is done as the following:
Column B formatted as Date (*3/14/2001)
Column C formatted as Custom (h:mm)
Column D "Total Time" is formatted as number (2 decimal places)
Formula in first D cell - "=IF(C12="","",24*(B12-B11)+24*(C12-C11))"
An Example:
Cell B10 CellC10 Cell D10
"Date" "Time" "Total Hours"
Cell B12 Cell C12 Cell D12
11/15/2009 16:04 =IF(C12="","",24*(B13-B12)+24*(B13-B12))
Cell B13 Cell C13 D13
11/15/2009 22:24 Blank

Here is what it looks like:
Date Time Total Hours
11/15/2009 16:04 6.33
11/15/2009 22:24

hth

"Moony" wrote:

> .
>

[Leung]

I think you can undersatnd all the small part for the formula. but just don't
know figure out which part get error.

you can highlight the part to evaluate it by click F9 key.

e.g. if (a-b = c, d,e)
so you high like c then press F9 then you will see the value. in case it
cannot be evaluted, it will show error. then you drill down which part
causing the error.

Somtimes is the function problem. but this way of debugging will help you a
lot.

"Moony" wrote:

> .
>