Calculation error in Workhours

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
 
S

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.
 
A

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
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top