Solved

Calculate Working Time Between Two Dates

Posted on 2011-03-23
16
278 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
[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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

622 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