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.

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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 ;-)

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?

Total Days: ((((DateDiff("d",[StartDat

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.

ScreenShot-DaysBooks.jpg

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.

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, 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.

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.

VBA.txt

/gustav

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.

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 trialEach 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.

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.

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.

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)

'*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!!!

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

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.

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.

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.

Huge, huge, huge thanks to both you and gustav for all your input and ideas.

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.

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.

Thanks again!!!

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.

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.

Microsoft Access

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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?

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.