Solved

Calculate Working Time Between Two Dates

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

813 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

14 Experts available now in Live!

Get 1:1 Help Now