• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Calculate Working Hours Between DateCreated and DateAssigned

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
greentatertot1970
Asked:
greentatertot1970
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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