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

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
LVL 1
fabi2004CIOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
3 days @ 12 hrs/day = 4.5 days @ 8 hrs/day
I'm OK with that but I don't understand how you are getting to 6 days.  What is the calculation for that?  There are 30 days in November or 26 if you start counting on the 4th.  How are you breaking that into weeks?  There are three full weeks and two partial weeks in those 26 days.  

When converting calendar days to weeks, what day does your week start on?  How do you handle the first and last weeks of the year which both may be partial?

Why would you calculate as days rather than hours?
So, 1/1/17 through 1/15/17 = 15 days (instead of 15-1 = 14)
That equates to:
DateDiff)"d", Date1, Date2) + 1

PS - Based on this description, you absolutely do not need those MSysObjects table from your other post.
0
fabi2004CIOAuthor Commented:
I'm sure I just confused everyone. I am so sorry!!!

The count of days in Nov should be 27. The number of 12 hour work days is (3 per week) (27/7)*3= 11.57.  The number of 8 hour work days is (12/8)*3, so for the month of Nov there are 17.36 8hr-workdays.

I don't take into account which day of the week the calendar starts on.  I'm only counting total number of days in the month and dividing it by 7 to get the number of weeks.

Then I take the number of weeks and multiply by number of worked days per week.

Finally, I take into account how many hours are worked per day so I can find my 8-hr days count.

I'm not sure that using hours in the calculation would significantly simplify the formula.

DateDiff("d", Date1, Date2) + 1 <--- that part I know ;-)
0
fabi2004CIOAuthor Commented:
I need to know how to count the days in a date range when the beginning date and ending date may not necessarily be the 1st and last day of the month.  I need to take into account that each month may have 30, 31 or 28 days (not counting leap year).  The date range may span many months as well as cross over year marks.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

fabi2004CIOAuthor Commented:
Also, I don't have to do this calculation in a query.  I could do it in a form's control box.  But I'll have to use the calculation over and over again so maybe a function?
0
PatHartmanCommented:
I need to know how to count the days in a date range when the beginning date and ending date may not necessarily be the 1st and last day of the month
DateDiff() does that as long as you ask for the answer in days.  Then you need to add 1 as I showed in my first example because you are counting both the start date and end date of the range.

How do you envision running this?  Are you going to enter a date range and expect one number to be returned regardless of how many months the period spans or are you expecting a recordset to be returned with one record per month the date range spans?
0
fabi2004CIOAuthor Commented:
I can get the the total count of days between the two dates, using this:

Total Days: ((((DateDiff("d",[StartDate],[EndDate]))+1)/7)*[DaysPerWeek]*[HoursPerDay])/8

but what I need is the total count of days for each month between the dates.  The dates will often span several months.

There are records in a table containing:
ID, StartDate, EndDate, DaysPerWeek, HoursPerDay

I need to show something like this in a form/report: (for ID:1,  StartDate: 9/11/16 and EndDate: 11/5/16, DaysPerWeek: 5, HoursPerDay: 8)
Month     Total Days
Sep               20
Oct                31
Nov               5

Again, It can be a query, a function, a sub routine.  Anything is fine as long as I can run the calculation over and over again.  Many other results will depend on total day count of each month.
0
fabi2004CIOAuthor Commented:
Here's a picture of a sample result set.  It is a form with search fields/filters and summary data in the header.  Datasheet view of results in the details.
ScreenShot-DaysBooks.jpg
0
Jason clarkDBA FreelancerCommented:
Calculating Working Days you may look the script on this page not sure this works for you: https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx
1
fabi2004CIOAuthor Commented:
Thanks Jason, I've looked at that one several times.  I've been all over the Internet trying to find something that will work or that I can modify to make it work for what I need.

I keep thinking that this is going to have to be some sort of VBA function that I can call from a procedure.  But my VBA skills are elementary at best so I'm having a difficult time creating the looping function I'm going to need.

I can't think of a way to do this date manupulation with Access's T-SQL.
0
PatHartmanCommented:
I don't have time to write this for you but you are probably going to need VBA.  You are taking one record and making multiple outputs.  An alternative would be joining to a "date" table but you would have to build the table to contain every date in the date range you input.  This table could be static but you would need to keep adding future records as necessary.  If you build this table you can join to the date field using cross join with criteria.  TableDate Between RangeStartDate and RangeEndDate.  Then you could summarize the records returned.
0
Gustav BrockCIOCommented:
You can use the method here as a start:

Excluded weekend days from a field that counts the number of days between 2 date fields

However, one must know which three days per week are working days. If you adjust the function as-is to three working days, these will be Monday-Wednesday.

And why do exclude both start and end date? You will miss one day.

Also, I guess your calculation here should read: 27 / 7 * 3 / * 12 / 8

The number of 12 hour work days is (3 per week) (27/7)*3= 11.57.  The number of 8 hour work days is (12/8)*3, so for the month of Nov there are 17.36 8hr-workdays.

/gustav
0
TusitalaCommented:
Hi,

The below function is supposed to return the number of days between two dates with parameters to allow for weekends and holidays.  

Please be aware that I did not write it nor have I used it.  I do not know who wrote it so I cannot properly attribute credit to the person that did.  

Maybe some of the other Experts could comment on its suitability to your problem.

Public Function Networkdays2(StartDate As Date, EndDate As Date, _
    ExcludeDaysOfWeek As Long, Optional Holidays As Variant) As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NetWorkdays2
' This function calcluates the number of days between StartDate and EndDate
' excluding those days of the week specified by ExcludeDaysOfWeek and
' optionally excluding dates in Holidays.

' ExcludeDaysOfWeek is a value from the table below.
'       1  = Sunday     = 2 ^ (vbSunday - 1)
'       2  = Monday     = 2 ^ (vbMonday - 1)
'       4  = Tuesday    = 2 ^ (vbTuesday - 1)
'       8  = Wednesday  = 2 ^ (vbWednesday - 1)
'       16 = Thursday   = 2 ^ (vbThursday - 1)
'       32 = Friday     = 2 ^ (vbFriday - 1)
'       64 = Saturday   = 2 ^ (vbSaturday - 1)
' To exclude multiple days, add the values in the table together. For example,
' to exclude Mondays and Wednesdays, set ExcludeDaysOfWeek to 10 = 8 + 2 =
' Monday + Wednesday.
' If StartDate is less than or equal to EndDate, the result is positive. If
' StartDate is greater than EndDate, the result is negative. If either
' StartDate or EndDate is less than or equal to 0, the result is a
' #NUM error. If ExcludeDaysOfWeek is less than 0 or greater than or
' equal to 127 (all days excluded), the result is a #NUM error.
' Holidays is optional and may be a single constant value, an array of values,
' or a worksheet range of cells.
' This function can be used as a replacement for the NETWORKDAYS worksheet
' function. With NETWORKDAYS, the excluded days of week are hard coded
' as Saturday and Sunday. You cannot exlcude other days of the week. This
' function allows you to exclude any number of days of the week (with the
' exception of excluding all days of week), from 0 to 6 days. If
' ExcludeDaysOfWeek = 65 (Sunday + Saturday), the result is the same as
' NETWORKDAYS.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
Dim TestDayOfWeek As Long
Dim TestDate As Date
Dim Count As Long
Dim Stp As Long
Dim Holiday As Variant
Dim Exclude As Boolean

If ExcludeDaysOfWeek < 0 Or ExcludeDaysOfWeek >= 127 Then
    ' invalid value for ExcludeDaysOfWeek. get out with error.
    Networkdays2 = 0
    Exit Function
End If

If StartDate <= 0 Or EndDate <= 0 Then
    ' invalid date. get out with error.
    Networkdays2 = 0
    Exit Function
End If

' set the value used for the Step in
' the For loop.
If StartDate <= EndDate Then
    Stp = 1
Else
    Stp = -1
End If

For TestDate = StartDate To EndDate Step Stp
    ' get the bit pattern of the weekday of TestDate
    TestDayOfWeek = 2 ^ (WeekDay(TestDate, vbSunday) - 1)
    If (TestDayOfWeek And ExcludeDaysOfWeek) = 0 Then
        ' do not exclude this day of week
        If IsMissing(Holidays) = True Then
            ' count day
            Count = Count + 1
        Else
            Exclude = False
            ' holidays provided. test date for holiday.
            If IsObject(Holidays) = True Then
                ' assume Excel.Range
                For Each Holiday In Holidays
                    If Holiday.Value = TestDate Then
                        Exclude = True
                        Exit For
                    End If
                Next Holiday
            Else
                ' not an Excel.Range
                If IsArray(Holidays) = True Then
                    For Each Holiday In Holidays
                        If Int(Holiday) = TestDate Then
                            Exclude = True
                            Exit For
                        End If
                    Next Holiday
                Else
                    ' not an array or range, assume single value
                    If TestDate = Holidays Then
                        Exclude = True
                    End If
                End If
            End If
            If Exclude = False Then
                Count = Count + 1
            End If
        End If
    Else
        ' excluded day of week. do nothing
    End If
Next TestDate
' return the result, positive or negative based on Stp.
Networkdays2 = Count * Stp

End Function

Open in new window


Let us know how you go.
~Tala~
1
PatHartmanCommented:
Gus, fabi2004 want's multiple results from one date range and that solution provides one number covering the entire range.  Look back at his answer to me in ID: 42072450
0
Gustav BrockCIOCommented:
Yes thanks, I saw that Pat, that's where I picked the example calculation.
To cover each month individually, you could use a loop.

My questions remain open.

/gustav
0
PatHartmanCommented:
Correct but that brings us back to using a VBA solution rather than a query solution.  I can't say that I understand the requirement but it seems that fabi would prefer a SQL solution which I offered.  He hasn't gotten back to us so I don't know which option to flesh out.
0
Gustav BrockCIOCommented:
I would rule out pure SQL. While it probably can be done with some trouble, it will lead to uncomprehensive and difficult to manage SQL. Speaking of experience.

/gustav
0
fabi2004CIOAuthor Commented:
Hi guys, sorry it took me so long to get back to you.

Gustav, you provided the original solution to this problem.  It turns out that it won't be a feasible solution as this database grows.  But I still very much appreciate your initial help with this.

Pat, I spent most of yesterday trying to work up a VBA solution (with the help of code freely posted online).  I have a mess of notes and workups in notepad.  I'm not sure whether it's worth it to post it here until I have something more solid.

My latest (hairbrained?) idea is to simulate a temp table using VBA to clear it out at the start of each run of the code, populate it with each record's multiple results, then grab those results using a link via query.

I did previously consider a table similar to the one you mention above but it just seems like too much extra when the calculation could/should be generated easily with some code.
0
fabi2004CIOAuthor Commented:
Tusitala, thank you so much for your post.  

I've seen that solution and various others as I've scoured the Internet for solutions.  Unfortunately, none of them seem to do exactly what I need them to with no easy way to modify them.
0
fabi2004CIOAuthor Commented:
This db is still in development, so if I have to modify the tables or the way the data is stored, I can.  But, honestly, it's pretty far along so if I can avoid that, I will.
0
fabi2004CIOAuthor Commented:
For kicks and grins, I'm attaching my notepad for the code.  Don't laugh too hard at my coding skills. The top part of the notes describes what I'm trying to code.
VBA.txt
0
Gustav BrockCIOCommented:
Well, as long as you can't define which three days of a week are workdays, you can't obtain a result, as - except for February in common years - no month has an integer count of weeks.

/gustav
0
PatHartmanCommented:
Let me explain the SQL Solution in more detail.
1. Create a table of dates.  One row for each date.  Keep in mind that you'll have to add to this table over time.
2. Create a new query and add the Dates table and your other table.  There is nothing to join on so this will produce a Cartesian Product.
3. Add criteria that selects Dates from the date table where the dates are between the start and end date of the range in your main table.  Save the query.
4. Create another query that selects the #3 query and summarize it by month.  You'll end up with 1 row per month.  
5. Create another query that selects the #4 query and add your calculations.

You can do this with fewer queries but the queries become more complex and harder to test so I tend to do them in baby steps.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fabi2004CIOAuthor Commented:
Gustav, I can redesign the tables so that we can choose exactly which days of the wee each contractor works.  But, in my opinion, that adds quite a bit of complexity to the queries without producing valuable results.

Each contractor may work anywhere from 1 to 7 days per week between a date range.  The contractor may also work more or less than 8 hours per day.

The results we need are TotalDaysWorked/Month Revenue/Month, Expenses/Month.  There are a few other predictive results needed but these three are the main focus.

The margin of error we'd get by using a number of weeks with two decimal points is negligible.  In fact, I've been using ROUND() for most calculations.  It gets the figures "close enough" as this is meant to be a forecasting tool as opposed to a financial one.
0
fabi2004CIOAuthor Commented:
Pat, I understand what you're saying with the dates table.  I've had that on the back burner for months.

It honestly seems like so much trouble to go through when I'm fairly certain a VBA loop could produce the results.

I think a loop (maybe a nested loop) using date functions should be able to count the days in each month of the range.

I'm going to work on it again tomorrow.  But I know I'll need VBA help even if I come up with something feasible, the code will trip me up since I only know the most basic of programming.

Is there maybe a VBA topic I should explore or list subsequent questions on?

I'm not giving up on the table idea.  I'm only in resistance mode right now.  I keep picturing a table with 50-100 years worth of day records and simply cringe at the idea.
0
Gustav BrockCIOCommented:
If a contractor can work three days a week, but you don't know which weekdays, the days count can be be anywhere between 12 and 15 for a month of 31 days, thus it makes little sense to calculate anything with two decimals.

Also, if it can vary that much, I see no way around recording the various days/hours along the contract period in a table, and then match this with a permanent or generated calendar table along Pat's suggestion. It should be pretty straight-forward to do this in VBA.

I'm busy with real work know, but you should be able to expand on my function bit by bit until success.

If you could provide a test table with data and the expected output, I might find time to look into it.

/gustav
0
PatHartmanCommented:
If you are trying to figure out what work was done during a given period, why are you not looking at actual timekeeping logs?  Don't people record their time?  

If you are trying to forecast, then that's a different problem.  We can't tell from your description.
0
fabi2004CIOAuthor Commented:
Pat, yes, this db will be used as a forecasting tool.  Contractors scheduled to work months in advance, what is the expected revenue, expenses for each month moving forward.

Gustav, I'm a little confused about your concern of which exact days of the week each contractor works.  The formula is pretty straight forward.

TotalDaysInMonth / 7 = TotalWeeksInMonth

TotalWeeksInMonth * NumDaysPerWeekWorked = WorkedDaysPerMonth

Then, to get the Work Days converted to 8 hours days:

WorkedDaysPerMonth * NumHoursPerDayWorked / 8 hours = Num8HourDaysWorkedPerMonth

The problem isn't finding out which particular days of the week the contractor is scheduled to work.  We'll accept that margin of error.  The problem is me finding a way to calculate using any random range of dates which could span months and even cross over a year mark.

I need to find the last day of the month, the total number of days in a month, and the first day of a month, while looping through the months in the range. (I think)
0
fabi2004CIOAuthor Commented:
This is the description of what I'm trying to do that I have in that VBA.txt file attached earlier in case you didn't open it.

'*Given a date range
'*Count the days in each month
'*Example: Using StartDate 1/15/17, EndDate 4/3/17 --- Jan = 17 days, Feb = 28 days, Mar = 31 days, Apr = 3 days
'----------------------------------------------------------
'**For each month count of days, calculate number of weeks
'**Convert to number of weeks by diving number of days for each month by 7 days in a week
'**Example: Feb = 28 days = 28/7 = 4
'----------------------------------------------------------
'***For each month count of weeks, calculate number of work days
'***Multiply by 'number of work days per week' data in table
'***Example:  If DaysPerWeek = 3, then Feb = 4 weeks * 3 days = 12 work days
'----------------------------------------------------------
'****Given 'HoursPerDay' in table, convert work days into 8 hour days
'****Example: If HoursPerDay = 12, then Feb = 12 work days * (12 hrs/8hrs) = 18 (8 hour) work days
'----------------------------------------------------------
'*****Each monthly work-day count should be output to a form/report which will include other monthly related information
'*****Will also need total of (8 hr) work days in range to use for other calculations like dividing total expenses into monthly sums
'----------------------------------------------------------
'----------------------------------------------------------
'----------------------------------------------------------

I might try uploading a sample db with only the pertinent tables later today.

Thanks so much for staying with me on this!  I really appreciate your help and ideas!!!
0
PatHartmanCommented:
If you use the dates table I suggested, you can do this on the fly with queries.  

If you go the VBA route, you will need to create a separate table to hold the results of your calculations so you can display them on a form.  You have to consider that you are working in a multi-user environment and some other user might be doing the same thing so your table needs to be able to identify which person or "batch" so records can be selected for display and then deleted at some point if you don't need permanent storage.

To do the VBA loop.  Start by extracting the start and end dates of the first month.  Then in the loop do the calculation for that period, write out the resulting calculated results, calculate the next month period, checking to make sure you haven't gone past the end date.  Both the first and last months could be "short" but the intervening months should be full months  Loop.
1
Gustav BrockCIOCommented:
The problem isn't finding out which particular days of the week the contractor is scheduled to work.  We'll accept that margin of error.

OK, but then - as said - it makes no sense to calculate with two decimals.

/gustav
0
fabi2004CIOAuthor Commented:
Pat,

For the table solution, I would create a Dates table with a single field listing each day as a date, ie. 1/1/15, 1/2/15, 1/3/15 and on for however many years I want to initially populate it.  

Is this correct?

I don't know the SQL for your #4. Create another query that selects the #3 query and summarize it by month.

At number 5, I need to be able to run this for each AssignmentID in the Assignments table, so I assume I carry that ID field through all the date queries?

For the VBA solution.  I know what I need to do.  I just don't know how to code it.  I do alright with SQL but my VBA is basic at best.
0
fabi2004CIOAuthor Commented:
gustav, I'm rounding the decimals off anyways so it's not a big deal
0
PatHartmanCommented:
My explanation assumed that you would be using the QBE.  Access queries can select tables or querydefs interchangeably for most purposes.  So in the QBE dialog where you select tables, change the tab and select a query.

Regarding the VBA.  If you don't know VBA well enough to attempt it based on my suggestions, you should probably initiate a GIG and pay someone to write it for you.  I don't take Gigs so it can't be me.  We are all volunteers here and although many experts write code as part of their answers, it is generally "air code", untested, and generic.  What you are asking for is specific for your project and you are getting paid to produce it.
0
fabi2004CIOAuthor Commented:
I can and do use the QBE.  My question wasn't how to add a query to the QBE.  It was how to "summarize by month" given the previous query.

Believe it or not, the very first time I came to EE with this question, my first post was a GIG request.  I was told to first post the question and then come back if I couldn't get an answer.

I posted the question and gustav solved it, but as my db grew, that solution created other problems.  So I came back with the same question.

I have some VBA code lined out.  Maybe I'll post it over on the VBA topic to see if anyone feels like playing with it to get it working.
0
fabi2004CIOAuthor Commented:
Pat, I'm going to take your dates table solution as the one for this question.

Huge, huge, huge thanks to both you and gustav for all your input and ideas.
0
PatHartmanCommented:
Sorry, your actual question wasn't clear.  Press the big sigma button and that changes the query to a "totals" query and adds "Group By" to all selected fields.  You can change "Group By" to Sum, Avg, First, etc or whatever makes sense for an individual field.

As I said, most people will just post code they have or even write generic code but at some point the question becomes a Gig and that is what I thought was happening.  If you want to take a stab at the code we'll be happy to help.
0
fabi2004CIOAuthor Commented:
Not real sure how to "group by" or "summarize" by month in a query.  I can do it in a report and I am familiar with totals queries.  So I'm sure I'll figure it out.  Thanks again Pat.  It's awesome that so many people here are willing to spend time helping others.

I'm going to give the VBA topic a shot with my question.  I feel like I'm so close to a solution, yet it's not coming together.
0
Gustav BrockCIOCommented:
Oh, I was about preparing a full solution ...
Driving home.

/gustav
0
fabi2004CIOAuthor Commented:
Gustav, I'll be posting the question on the VBA topic along with the code I have so far.

Thanks again!!!
0
PatHartmanCommented:
Not real sure how to "group by" or "summarize" by month in a query
Use the Format() function to extract year and month from the date field.

Select ..., Format(somedate, "yyyymm") as YM From YourTable;  

Always include year when grouping by month or you'll end up with Dec 16 coming after Jan 17 instead of before.
0
fabi2004CIOAuthor Commented:
Thanks Pat.  I'd already added two group by columns, Year(DateDay) and Month(DayDate) to the query and it worked fine.  But I like your way better.

By the way, as I waited for feedback on my code, I went ahead and tried your date table suggestion.  I was a lot easier to create that Date table than I thought it would be.  I have 40 years in it and I think that will more than cover the life of this db.  If not, someone can always expand it in the future.

This works great and the ultimate query now runs much, much faster which was the game plan.  So, thank you very, very much for your help.
0
PatHartmanCommented:
You're welcome.  The dates or sequence numbers table is a useful method when you need controlled duplication of data.    I'm currently using it for a purpose similar to yours.  I need a count by day of active customers which will then be used to feed forecasting algorithms so my client can determine how much energy they need to buy in the near and far future.  I have also used the sequence number table to generate multiple labels.  So, one order needs 3 labels (one for each box) and another needs 1 and another needs 4, etc.
0
fabi2004CIOAuthor Commented:
That sounds intense.  You also mentioned you're working with millions of records.  That table would be a life saver in that scenario.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.