A
Andrew
I am using the folloing formula to calculate workhours on the columns below.
On most of the calculation the formula works 100% but in some cases, when
column O is a day later than column A I get incorrect calculation.
For the Date / Time below the answer is 14.98 hours while the hours should
be 2 hours 1 minute. Not all the calculations which is over a 2 day period
calculates incorrectly.
Please help
=IF(OR(O321<1,AND(INT(A321)=INT(O321),NOT(ISNA(MATCH(INT(A321),List!A$9:A$24,0))))),0,ABS(IF(INT(A321)=INT(O321),ROUND(24*(O321-A321),2),
(24*(Q321-P321)*
(MAX(NETWORKDAYS(A321+1,O321-1,List!A$9:A$24),0)+
INT(24*(((O321-INT(O321))-
(A323-INT(A321)))+(Q321-P321))/(24*(Q321-P321))))+
MOD(ROUND(((24*(O321-INT(O321)))-24*P321)+
(24*Q321-(24*(A321-INT(A321)))),2),
ROUND((24*(Q321-P321)),2))))))
Column A Column O
Column P Column Q
2008/10/06 03:02:00 PM 2008/10/07 08:33:00 AM 08:00
16:30
On most of the calculation the formula works 100% but in some cases, when
column O is a day later than column A I get incorrect calculation.
For the Date / Time below the answer is 14.98 hours while the hours should
be 2 hours 1 minute. Not all the calculations which is over a 2 day period
calculates incorrectly.
Please help
=IF(OR(O321<1,AND(INT(A321)=INT(O321),NOT(ISNA(MATCH(INT(A321),List!A$9:A$24,0))))),0,ABS(IF(INT(A321)=INT(O321),ROUND(24*(O321-A321),2),
(24*(Q321-P321)*
(MAX(NETWORKDAYS(A321+1,O321-1,List!A$9:A$24),0)+
INT(24*(((O321-INT(O321))-
(A323-INT(A321)))+(Q321-P321))/(24*(Q321-P321))))+
MOD(ROUND(((24*(O321-INT(O321)))-24*P321)+
(24*Q321-(24*(A321-INT(A321)))),2),
ROUND((24*(Q321-P321)),2))))))
Column A Column O
Column P Column Q
2008/10/06 03:02:00 PM 2008/10/07 08:33:00 AM 08:00
16:30