If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
workday, networkday or something else?
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? |
#2
|
|||
|
|||
workday, networkday or something else?
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? |
#3
|
|||
|
|||
workday, networkday or something else?
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? |
#4
|
|||
|
|||
workday, networkday or something else?
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? |
#5
|
|||
|
|||
workday, networkday or something else?
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? |
#6
|
|||
|
|||
workday, networkday or something else?
Hello,
I suggest to use my UDF count_hours: http://sulprobil.com/html/count_hours.html Regards, Bernd |
Thread Tools | |
Display Modes | |
|
|