Solved

Calculate Working Time Between Two Dates

Posted on 2011-03-23
16
275 Views
Last Modified: 2012-08-13
I have an assignment where I need to calculate the time (days, hours, miniutes) it has taken to complete an order.  The calculation needs to exclude working days and holidays which flucuate by country.  

I have the country, start (date/time) and end (date/itme) fields for the calculation and also tables with dates of country specific holidays and a table for country specific working days.  

I've seen other solutions provided on this site for calculating time between dates but not sure how to incorporate the international aspect of this task into a solution.

I need to complete this task very quickly so any and all assistance is greatly appreciated.

0
Comment
Question by:rsaphier
  • 9
  • 7
16 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35198892
Can you upload db with related tables?
0
 

Author Comment

by:rsaphier
ID: 35199087
As requested, attached is a sample of the data available.  Please let me know if you have any questions.
SampleData.accdb
0
 
LVL 40

Expert Comment

by:als315
ID: 35200030
One more question: Date and Time values are stored with time zone or it should be corrected?
For example:
Incident #      Group Name                               Open Date                       Close Date
13201749      DOU_HR_Russia/Caspian               10.31.2010 8:58:49 AM      31.01.2011 8:58:49
8:58 is local time or some "fixing server" time?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:rsaphier
ID: 35200126
Thank you for your response.  Your represenation of the data above is not as presented in the database.  It would appear that there is a format change on the Close Date of your data.

Incident #      Group Name      Open Date      Close Date
13201749      DOU_HR_Russia/Caspian      10/31/2010 8:58:49 AM      1/31/2011 8:58:49 AM

Also, I'm not quite clear on how a time zone would impact the calculation as there are 24 hours in each day, regardless of day.

Please let me know if you have further questions.



0
 
LVL 40

Expert Comment

by:als315
ID: 35200235
Problem is following:
is 8:58:49 AM local time for this region: DOU_HR_Russia/Caspian
or not.
Should I add to time calculations time zone difference or not?
0
 

Author Comment

by:rsaphier
ID: 35200318
No, please do not worry about time zone differences.  Thank you.
0
 
LVL 40

Expert Comment

by:als315
ID: 35201580
Test this sample. I have used only Begin and End from Tbl_Workweek (these values were converted to integer). It seems some data are not correct:
Group      NonWkDay      Begin      End
DOU_HR_CONT EUROPE      Sat      1      6
DOU_HR_UK/NORWAY      Sat      1      5
In both cases only one NonWkDay, but End is different.

I also have proposed holidays can't be in weekend.
Q1 query calculates holidays for interval. Q2 is resulting query. MinInt - interval in minutes.
SampleData.accdb
0
 

Author Comment

by:rsaphier
ID: 35201834
Yes, I do see that there is an error in the data.   The End for Norway should be 6.  Can you please tell me how to properly format the expression in Q2 view (MinInt) so that the results are the number of days, hours and minutes so that I can do validation for accuracy.
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 35202333
This is with additional column SampleData.accdb
0
 

Author Comment

by:rsaphier
ID: 35202798
I would like to verify the use of the table tbl_Workweek

Group      NonWkDay      Begin      End
DOU_HR_North America      SatSun      2      6
DOU_HR_Middle East      None      1      7
DOU_HR_EH_GMSC      Sat      1      6

Based on the above examples, are your calculations are based on the following:
Ex 1: begin Workweek on Monday and End Workweek on Friday
Ex 2: begin Workweek on Monday and End Workweek on Sunday
Ex 3: begin Workweek on Sunday and End Workweek on Friday

Just need to verify that we are on the same page for the intended use of this field.


0
 
LVL 40

Expert Comment

by:als315
ID: 35204618
I hope it is so :)
0
 

Author Comment

by:rsaphier
ID: 35208514
I'm seeing errors in the calculation and I have not been able to determine if it is a problem excluding the non working days or holidays.  In the results produced from the calculation below, there is a holiday for this group on 2/3/2011 with non working days of Saturday and Sunday (2 and 6).  I would expect to see a result of 3 Days X Hours X Minutes.

 Error in Calculation
0
 

Author Comment

by:rsaphier
ID: 35208805
Think I may have discovered the issue.  In Q1, TM is a negative value.  When used in Q2 expressions I think we should + rather than -.     Double negative = a positive.

I'll do a bit more validation .... hoping this was the problem.
0
 
LVL 40

Expert Comment

by:als315
ID: 35209606
Yes, it was my error.
0
 

Author Comment

by:rsaphier
ID: 35211819
I've tested a few records and believe that this is exactly what I need.  Thank you very much!  I truly appreciate the quick turn around.
0
 

Author Closing Comment

by:rsaphier
ID: 35211825
Thanks Again!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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