Adding and ISERROR to the following (large Formula)

  • Thread starter Thread starter Joe Cook
  • Start date Start date
J

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
 
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)
 
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
 
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
 
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
 
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
 
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
 
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
 
Hi
file is on the way back to you. Just simply adding the following to
your formula worked:
=IF(G8="",0",your_existing formula)
 
Back
Top