Solved

Calculate Working Time Between Two Dates

Posted on 2011-03-23
16
272 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 39

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 39

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
 

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 39

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 39

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

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 39

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 39

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now