turaks
asked on
Calculating working hours time span
Does anyone have a function to calculate working hours time span between 9am and 5.30pm using the 12 hour clock
I want to calculate averagewaitingtimes using the format below.
I want the calculation to exclude bank holidays and weekends and should not be the 24 hour format
The working hours are between 9am to 5.30pm
I want the calculation to stop at 5.30pm
e.g
datepricequoterequested -24/10/2011 at 15.00
datepricequotetodistributo r – 24/10/2011 at 16.00
datepricequotefromdistribu tor – 24/10/ 2011 at 17:00
datepricequoteprocessed -25/10/2011 at 10:00am
Stop counting from 17:30 and 09:00
e.g
15:00-16:00 1 hour at company
16:00-17:00 1 hour at distributor
17:00 – 17:30 ½ hour at company
09:00 -10:00 1 hour at company
Gives the company 2 ½ hrs
Format is:
datepricequoteprocessed - (datepricequotetodistribut or - datepricequotefromdistribu tor)- datepricequoterequested
e.g
25/10/2011 at 10:00am-(24/10/2011 at 16.00-– 24/10/ 2011 at 17:00)- 25/10/2011 at 10:00am
I want to calculate averagewaitingtimes using the format below.
I want the calculation to exclude bank holidays and weekends and should not be the 24 hour format
The working hours are between 9am to 5.30pm
I want the calculation to stop at 5.30pm
e.g
datepricequoterequested -24/10/2011 at 15.00
datepricequotetodistributo
datepricequotefromdistribu
datepricequoteprocessed -25/10/2011 at 10:00am
Stop counting from 17:30 and 09:00
e.g
15:00-16:00 1 hour at company
16:00-17:00 1 hour at distributor
17:00 – 17:30 ½ hour at company
09:00 -10:00 1 hour at company
Gives the company 2 ½ hrs
Format is:
datepricequoteprocessed - (datepricequotetodistribut
e.g
25/10/2011 at 10:00am-(24/10/2011 at 16.00-– 24/10/ 2011 at 17:00)- 25/10/2011 at 10:00am
can you post your table structure? (column names and types)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Weekends are easy to determine, but you would need a table with all the bank holidays in it.
The rest of the computation can be done in an expression to determine the total company time.
Presumably average would just be dividing that total company time by the total row count.
The rest of the computation can be done in an expression to determine the total company time.
Presumably average would just be dividing that total company time by the total row count.