Solved

Calculate Working Hours Between DateCreated and DateAssigned

Posted on 2011-02-18
1
314 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

786 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