workday, networkday or something else?

[AJ]

I have two date and time fields, the Q column is the Ordered Date and the T
colum is the Date Closed. We work 8 hours a day from 8-5. Im trying to
figure out how many hours / minutes it took someone to do the work from the
time it went ordered to the point it closed. Taking into account our work
day, which function should i use one of the ones i listed or other ones?

 

[Mike H]

Hi,

Try this.

the formula 'assumes' that you won't take/close any orders outside of the
workday. i.e. if you working days starts at 08:00 you won't take an order at
07:00 on that day.

Holidays is a named range of any holiday dates to exclude

=((NETWORKDAYS(Q1,T1,Holidays)-1)*("16:00"-"08:00")+MOD(T1,1)-MOD(Q1,1))*24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[AJ]

Mike,

here is the field for Q838: 3/3/2010 4:06:08 PM
here is the field for T838: 3/3/2010 8:22:42 PM
This is my fuction
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

I would expect 4 hours 16 minutes and 34 seconds and change but what i get
is 16:06

 

[AJ]

Mike,

This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

 

[Jakob]

Try
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))

--
Med hilsen


Jakob Austgulen
http://www.pointshop.no/austgulen

 

[Bernd P]

Hello,

I suggest to use my UDF count_hours:
http://sulprobil.com/html/count_hours.html

Regards,
Bernd