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

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

3 days @ 12 hrs/day = 4.5 days @ 8 hrs/dayI'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.

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 monthDateDiff() 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?

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

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
```

Let us know how you go.

~Tala~

To cover each month individually, you could use a loop.

My questions remain open.

/gustav

/gustav

/gustav

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

If you are trying to forecast, then that's a different problem. We can't tell from your description.

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.

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

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.

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.

Not real sure how to "group by" or "summarize" by month in a queryUse 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.