Solved

Calculate Working Hours Between DateCreated and DateAssigned

Posted on 2011-02-18
1
313 Views
Last Modified: 2012-05-11
I was able to find help on my question for Excel (
Example in Excel)...
But I am hoping to do the same in Access.
What I am needing to calculate are the working hours between a Date/Time of item creation and the Date/Time of item assignment.

The working hours are between 08:00 and 17:00, Monday through Friday. The formula needs to disregard weekend days/hours and weekday non-working hours.

Additionally it needs to account for Date/Time of item creation and Date/Time of item assignment that occurs outside of the hours of 08:00 and 17:00. (EXAMPLE: if the item was created at 08/02/10 06:15 it should be assumed/calculated as if it was created at 08/02/10 08:00; if the item was assigned at 08/02/10 19:24 is should be assumed/calculated as if it was assigned at 08/02/10 17:00 or 08/03/10 08:00 - whichever is easier or makes the most sense.)

Lastly, the results may be greater that 24 hours - this has also been problematic. I need to be able to display and perform additional functions (e.g. AVERAGE, MIN, MAX) on the cumulative results. Results greater that 24 hours seem to throw a wrench into things.
0
Comment
Question by:greentatertot1970
1 Comment
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34930723
see this similar thread..
the codes in the function AddWorkHours will get you started
just tweak it to suit your requirement


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23117579.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

863 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

25 Experts available now in Live!

Get 1:1 Help Now