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(MAT CH(INT(A321),List!A$9:A$24,0))))),0,ABS(IF(INT(A32 1)=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
--
Andrew

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.

--
Thanks,
Shane Devenshire


"Andrew" wrote:


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(MAT CH(INT(A321),List!A$9:A$24,0))))),0,ABS(IF(INT(A32 1)=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
--
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
--
Andrew


"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"Andrew" wrote:


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(MAT CH(INT(A321),List!A$9:A$24,0))))),0,ABS(IF(INT(A32 1)=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
--
Andrew

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)



On Tue, 14 Oct 2008 21:51:01 -0700, Andrew
wrote:


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(MA TCH(INT(A321),List!A$9:A$24,0))))),0,ABS(IF(INT(A3 21)=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