Adding and ISERROR to the following (large Formula)

[Joe Cook]

=IF(AND(INT(E8)=INT(G8),NOT(ISNA(MATCH(INT
(E8),HOLIDAYLIST,0)))),0,ABS(IF(INT(E8)=INT(G8),ROUND(24*
(G8-E8),2),
(24*(DAYEND-DAYSTART)*
(MAX(NETWORKDAYS(E8+1,G8-1,HOLIDAYLIST),0)+
INT(24*(((G8-INT(G8))-
(E8-INT(E8)))+(DAYEND-DAYSTART))/(24*(DAYEND-DAYSTART))))+
MOD(ROUND(((24*(G8-INT(E8)))-24*DAYSTART)+
(24*DAYEND-(24*(E8-INT(E8)))),2),
ROUND((24*(DAYEND-DAYSTART)),2))))))

The above formula was taken from Chip Pearsons site and
works well, we're calculating the work hours between two
dates and times entered as '19/05/04 12:54', the problem
is that some of the cells have the following

Start Time Finish Time Turnaround

17/05/2004 09:00 17/05/2004 10:00 3.0
17/05/2004 15:00 #VALUE
18/05/2004 09:00 18/05/2004 12:00 3.00

Some of the cases are still open, thus causing a #VALUE
to appear, this in turn causes problems for follow on
formulas

Is there any way to add an is ERROR formula to return a 0
value?

Many Thanks

Joe

 

[Frank Kabel]

Hi
try
=IF(ISNUMBER(G8),IF(AND(INT(E8)=INT(G8),NOT(ISNA(MATCH(INT
(E8),HOLIDAYLIST,0)))),0,ABS(IF(INT(E8)=INT(G8),ROUND(24*
(G8-E8),2),
(24*(DAYEND-DAYSTART)*
(MAX(NETWORKDAYS(E8+1,G8-1,HOLIDAYLIST),0)+
INT(24*(((G8-INT(G8))-
(E8-INT(E8)))+(DAYEND-DAYSTART))/(24*(DAYEND-DAYSTART))))+
MOD(ROUND(((24*(G8-INT(E8)))-24*DAYSTART)+
(24*DAYEND-(24*(E8-INT(E8)))),2),
ROUND((24*(DAYEND-DAYSTART)),2)))))),0)

 

[Guest]

Frank, thanks for the suggestion, this appears to work
with the cells containing #VALUE but when copied down to
include cells that actually have a proper turnaround time
(eg. 2.60) the value of 0.00 is returned, removng the
correct values.

All help appreciated.

Cheers

Joe

 

[Frank Kabel]

Hi
does your original formula work for these other cells?. As I haven't
checked your posted, original formula this formula may have an error as
the ISERROR function shouldn't cause this result

 

[Guest]

Just to let you know that the cells the foemula are
comparing are formatted as custom with the dd/mm/yyyy
hh:mm,

Regards

David

 

[Joe Cook]

Hi Frank, yes the original formula works fine - the only think I shoul
add is that the I'm asking the formula to compare two cells formatte
as custom, dd/mm/yyyy hh:mm to be precise.

Date In(Custom) Date Out(Custom) Turnaround
17/05/04 12:41 19/05/04 15:02 18.35

Don't know if that will make any difference?

I've enclosed a sample copy of the formula that work

=IF(AND(INT(E26)=INT(G26),NOT(ISNA(MATCH(INT(E26),HOLIDAYLIST,0)))),0,ABS(IF(INT(E26)=INT(G26),ROUND(24*(G26-E26),2),
(24*(DAYEND-DAYSTART)*
(MAX(NETWORKDAYS(E26+1,G26-1,HOLIDAYLIST),0)+
INT(24*(((G26-INT(G26))-
(E26-INT(E26)))+(DAYEND-DAYSTART))/(24*(DAYEND-DAYSTART))))+
MOD(ROUND(((24*(G26-INT(E26)))-24*DAYSTART)+
(24*DAYEND-(24*(E26-INT(E26)))),2),
ROUND((24*(DAYEND-DAYSTART)),2))))))

The above when used returns the 18.35 value that you see above.

Many Thanks

Davi

 

[Frank Kabel]

Hi
and which cell could contain an error value?

 

[Joe Cook]

Hi Frank, thanks for the speedy response, the layout is shown below, th
cell that would contain the error Value would be J8

Column E G J
Title Time In Time Out Turnaround

17/05/2004 12:41 19/05/2004 15:02 18:35

Its J8 where the #VALUE sign appear when cell G8 is blank

Hope this helps, if required I can upload a sample spreadsheet.

Regards

Jo

 

[Frank Kabel]

Hi
still not quite sure. Please do NOT upload an example. If you like
email me an example privately.
email: frank[dot]kabel[at]freenet[dot]de

and explain in your example file the desired results and show the
currently wrong results

 

[Joe Cook]

Frank, I've emailed you an example file.

Regards

Jo

 

[Frank Kabel]

Hi
file is on the way back to you. Just simply adding the following to
your formula worked:
=IF(G8="",0",your_existing formula)

 

[Joe Cook]

Thanks Frank for all your help, the file works great.

Cheers

Jo