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?

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" wrote:

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,

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



"Mike H" wrote:

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" wrote:

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,

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?





"Mike H" wrote:

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" wrote:

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?

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

--
Med hilsen


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


"AJ" skrev i melding
...
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?





"Mike H" wrote:

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" wrote:

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?

Hello,

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

Regards,
Bernd