Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculating working hours time span

Posted on 2012-03-19
3
Medium Priority
?
934 Views
Last Modified: 2012-03-31
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
datepricequotetodistributor  – 24/10/2011 at 16.00
datepricequotefromdistributor – 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 -  (datepricequotetodistributor  - datepricequotefromdistributor)- 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
0
Comment
Question by:turaks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37740281
can you post your table structure? (column names and types)
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 1000 total points
ID: 37740612
There's a blog post on a related subject at
http://pratchev.blogspot.com/2008/01/calculating-work-hours.html
and you may find something close in the business maths part at
http://www.totallysql.com/

If you do end up doing it yourself, I think you can best start by creating a dates table, that provides you with all the days that can be utilised (i.e. get weekends and holidays specified so you can skip them).

Then what you imply is that you provide the code with the quoteRequested and quoteProcessed datetimes and distributorOut and distributorIn datetimes, This effectively gives you two periods - from quoteRequested to distributorOut and from distributorIn to quoteProcessed. I think you can get the technique for these two from the blog and then add them ...

hth

Mike
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37742538
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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question