Calculation error in Workhours

[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

 

[ShaneDevenshire]

Hi,

Rather than have us try to figure out what your formula does, why don't you
tell us what it should do. For example, tell us how you determine work
hours.

 

[Andrew]

Column A is start date / time and column O is end date/time. Column P is
workhours start (08:00) and Column Q is workhours end (16:30).

The calculation should then when a start date and end date is captured
determine the work hours between 08:00 and 16:30.

As per the example below the start date was 15:02 on the 1st October (Work
hours for the first will then be 16:30 minus 15:02 = 1hour 28 min) and then
for the 2nd October the request was completed at 08:33 (work hours then 33
min) in total the request took 2 hour 1 min but when calculated 14.98 hours
were calculated

 

[Chip Pearson]

That looks like my formula from
http://www.cpearson.com/Excel/DateTimeWS.htm. Based on your example
values, the correct answer is 2.0167, which is 2 hours 1 mintues
(1:28:00 on the first day plus 0:33:00 on the second day = 2:01:00 =
2.0167 hours). The formula on the web page returns the correct answer.

Be sure to format the result as general or numeric, not as a date or
time.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)