troubleshooting Question

How to calculate the number of work days between two dates.

Avatar of fabi2004
fabi2004Flag for United States of America asked on
Microsoft Access
43 Comments1 Solution2707 ViewsLast Modified:
I need to calculate WorkDays in each month between two dates.

WorkDays are defined as 8 hours per day.

Some of the pertinent fields are:
AssignmentID
DateStart
DateEnd
DaysPerWeek
NumHoursPerDay

*note:  DateStart and DateEnd should be inclusive in the calculation.  ie. the date of DateStart counts as 1 day, the date of DateEnd also counts as 1 day. So, 1/1/17 through 1/15/17 = 15 days (instead of 15-1 = 14)

Sample data:
AssignmentID: 111
DateStart: 11/4/2016
DateEnd:  5/12/2017
NumDaysPerWeek:  3
NumHoursPerDay:  12

Results needed (for each month in the Assignment date range):
Month: Nov 2016
WorkDays: 6

Note:  3 days @ 12 hrs/day = 4.5 days @ 8 hrs/day so that the 4 days worked in Nov at 12 hrs/day equal 6 days if the work day is calculated based on an 8 hr day
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 43 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 43 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros