dates and times, tracking using

  • Thread starter Thread starter Krandall
  • Start date Start date
K

Krandall

My boss would like me to record the date and time an
order comes in and track how long it takes to be filled,
in hours and minutes using a 8-5 workday. It could take
a couple of days for the order to be filled. For
instance an order could come in on 10/17/03 at 2:03 pm.
However it wouldn't be filled until 10/21/03 at 10:04
AM. Is there a formula that would calculate the
necessary result (remembering not to count after 5 pm or
before 8 am and not counting weekends)? Is there a
specific way I would need to enter the date and time (ie.
seperate columns, both in the same column?). I'm
becoming very frustrated. Please help.
 
one way:

If you enter the date/times in one cell, e.g.:

A1: 10/17/03 02:03 pm
B1: 10/21/03 10:04 am

then

C1: =(NETWORKDAYS(A1,B1)-2)/3 + TIME(17,0,0) - MOD(A1,1) +
MOD(B1,1) - TIME(8,0,0)

or, rearranging and combining terms:

C1: =(NETWORKDAYS(A1,B1))/3-7/24-MOD(A1,1)+MOD(B1,1)
 
Back
Top