By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 434,933 Members | 1,243 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,933 IT Pros & Developers. It's quick & easy.

# Average number of workdays per month from table

 P: 7 I figured out how to calculate the work day given two values but all of my records have dates and i want to be able to calcuate the average time it takes to do a job per month so basically take all the calculations add them up and divide by the total does anyone know how to do that. Expand|Select|Wrap|Line Numbers   Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '.................................................................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '.................................................................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database     Set DB = CurrentDb     Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)       'StartDate = StartDate + 1     'To count StartDate as the 1st day comment out the line above     intCount = 0       Do While StartDate <= EndDate         rst.FindFirst "[HolidayDate] = #" & StartDate & "#"         If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then         If rst.NoMatch Then             intCount = intCount + 1         End If         StartDate = StartDate + 1     Loop       WorkingDays2 = intCount Exit_WorkingDays2:     Exit Function Err_WorkingDays2:     Select Case Err     Case Else         MsgBox Err.Description     Resume Exit_WorkingDays2     End Select End Function   Jun 13 '07 #1
Share this Question
1 Reply

 Expert Mod 15k+ P: 31,492 Perhaps you wouldn't mind rewriting the question with some punctuation. Jul 4 '07 #2

### Post your reply

Sign in to post your reply or Sign up for a free account.