# Thread: Help Using a Calculation for Workdays Between Two Dates

1. Registered User
Join Date
Oct 2006
Posts
26

## Unanswered: Help Using a Calculation for Workdays Between Two Dates

Okay, I created a new module with the following code which is used to calculate workdays that exist between a start and end date contained in a table. I don't know if I need it in a module or not. I just need to calculate the number of workdays between two dates. It would be best if I didn't have to use a module because I am new to modules. According to this code, what do I need to name my table and fields in order to get this to work. Also, how do I get access to utilize the module? Where does the calculation get entered into my database? I need the calculated number of workdays to appear in a 3rd field of my table which is NumberWorkDays (1st two fields StartDate & EndDate, or whatever they are supposed to be named). Any help is greatly appreciated.

Public Function GetWorkDays(dtStartDate As Date, dtEndDate As Date) As Integer
Dim intDaysToSaturday As Integer
Dim intDaysToSunday As Integer
Dim intWeeks As Integer
Dim intWorkDays As Integer
Dim dtX As Date

'Count work days from start date until Saturday of that week. intDaysToSaturday = vbSaturday - Weekday(dtStartDate, vbSunday) dtX = DateAdd("d", intDaysToSaturday + 1, dtStartDate) If intDaysToSaturday >= 5 Then intWorkDays = 5 Else intWorkDays = intDaysToSaturday
End If

'Count number of weeks between start date and end date, not counting 'the last week.intWeeks = DateDiff("ww", dtX, dtEndDate, vbSunday)

'Count number of work days in the last week. intDaysToSunday = Weekday(dtEndDate, vbSunday) - vbSunday If intDaysToSunday >= 5 Then intWorkDays = intWorkDays + 5
Else: intWorkDays = intWorkDays + intDaysToSunday
End If

'Multiply in all the weeks inbetween the first week and last week. intWorkDays = intWorkDays (intWeeks * 5)

'Subtract out Holidays intWorkDays = intWorkDays - DCount("*", "Holidays", "HolidayDate >= #" & dtStartDate & _ "# AND HolidayDate <= #" & dtEndDate & "#") GetWorkDays = intWorkDays

End Function

I also know that the db needs a "Holidays" table with fields Holiday
and HolidayDate.

2. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
don't store the calculated value in the table (see hundreds of posts here explaining why) unless you have a very good reason.

for reports etc you can generate the days with a query using your function (i assume it works - did not check). for this to happen, the function must be public and in a module.

your query would look something like
SELECT [thisfield], [thatfield], GetWorkDays([startdateField], [enddateField]) As [WorkDays] FROM [sometable];

izy

LATER: added [] around all of them to avoid inadvertant suggestion that [] required for the function. if no spaces in the name you can drop the [] around it.

LATER STILL: your function will not work as posted. you have comment lines running in to code lines:
'Count work days from start date until Saturday of that week. intDaysToSaturday = vbSaturday blah blah
needs to be
'Count work days from start date until Saturday of that week.
intDaysToSaturday = vbSaturday blah blah

but this is probably just a posting artifact
Last edited by izyrider; 10-27-06 at 12:45.

3. Registered User
Join Date
Oct 2006
Posts
26
Here is my goal. I have what is a "realtime" record of how much money is available for a few different projects. Salaries are paid roughly every 2 weeks or 10 business days.

There is a "Project Assignment" table in which different personnel are assigned to different projects for varying periods of time. There are field names for "EmployeeName", "Project", "Start Date", "End Date", and "Wage Rate".

I see how I can use a query to calculate the amount of money they will be paid for the period of the project.

My main problem now is that I need a way to have the information from the "Project Assignment" table automatically generate a new Record in my "Expense" table for the pay periods twice a month. This dollar amount will be close to actual but does not have to be as I have an employee who manually updates the exact dollar amount for all expenses each week.

I also have no clue on how to use a module. Thank you for your help.

4. Registered User
Join Date
Oct 2006
Posts
26
Maybe a better solution would be for me to find a way to apply thier monthly salary prorated for the days that they are assigned to the project. I still would need a way to have that amount automatically generate an expenditure on the 1st and 15th of each month.

5. Registered User
Join Date
Oct 2006
Posts
26
If you are looking for a similar solution please check the following link.

Taurus is just great!