Getting a very complicated formula to calculate only if NOT(ISBLANK)

[kaldrazidrim]

H4 is a date/time stamp I have saved as a macro. Returns 12/28/2011 10:47:00 AM.

I4 is the same macro and returns 12/28/2011 10:48:00 AM

J4 calculates the difference between the two (I4-J4), but only recognizes business hours and business days (Monday-Friday, 8:00 am to 5:00 pm)

I only want J4 to calculate if I4 is NOT BLANK.

These are in a table so J4 is trying to calculate when there is data in H4, but not I4, and returning a large number like 981583.22

When I try to apply IF(ISBLANK) logic to the formula in J4, I get an error that it exceeds 255 characters, even though it works fine if I am not trying to put the IF(ISBLANK) logic in.

Here is the formula in J4. I want it to automatically calculate if there is data in I4. Otherwise, I want it to return 0.

Thank you!

=IF(AND(INT(H4)=INT(I4),NOT(ISNA(MATCH(INT(H4),HolidayList,0)))),0,ABS(IF(INT(H4)=INT(I4),ROUND(24*(I4-H4),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(H4+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(I4))-
(H4-INT(H4)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(I4-INT(I4)))-24*DayStart)+
(24*DayEnd-(24*(H4-INT(H4)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

 

[shg]

Barry Houdini is a master of date calculations. This is one of his (if I haven't butchered it), and assumes that the timestamps are workdays duing work hours:

=(NETWORKDAYS(dateBeg, dateEnd, Holidays) - 1) * (timeEnd - timeBeg) + MOD(dateBeg, 1) - MOD(dateEnd, 1)

dateBeg and dateEnd are your time stamps, timeEnd and timeBeg are 17:00 and 08:00 respectively.

You can add a test:

=if(i4=0, 0, thatformula)