Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Calculate Working Time Between Two Dates

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
rsaphier
Asked:
rsaphier
  • 9
  • 7
1 Solution
 
als315Commented:
Can you upload db with related tables?
0
 
rsaphierAuthor Commented:
As requested, attached is a sample of the data available.  Please let me know if you have any questions.
SampleData.accdb
0
 
als315Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rsaphierAuthor Commented:
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
 
als315Commented:
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
 
rsaphierAuthor Commented:
No, please do not worry about time zone differences.  Thank you.
0
 
als315Commented:
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
 
rsaphierAuthor Commented:
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
 
als315Commented:
This is with additional column SampleData.accdb
0
 
rsaphierAuthor Commented:
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
 
als315Commented:
I hope it is so :)
0
 
rsaphierAuthor Commented:
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
 
rsaphierAuthor Commented:
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
 
als315Commented:
Yes, it was my error.
0
 
rsaphierAuthor Commented:
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
 
rsaphierAuthor Commented:
Thanks Again!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now