workday, networkday or something else?
in [Functions]
|
From: AJ on 14 Apr 2010 10:08 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?
From: Mike H on 14 Apr 2010 10:55 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? >
From: AJ on 15 Apr 2010 08:45 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? > >
From: AJ on 15 Apr 2010 08:48 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? > >
From: Jakob on 19 Apr 2010 21:50 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" <AJ(a)discussions.microsoft.com> skrev i melding news:0CF59924-188D-4DA9-B2F2-2E8B599243C0(a)microsoft.com... > 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? >> >
|
Next
|
Last
Pages: 1 2 Prev: consolidating data for a report - HELP - pivot tables no good for Next: If argument |