By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 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
  1.  
  2. Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
  3. '....................................................................
  4. ' Name: WorkingDays2
  5. ' Inputs: StartDate As Date
  6. ' EndDate As Date
  7. ' Returns: Integer
  8. ' Author: Arvin Meyer
  9. ' Date: May 5,2002
  10. ' Comment: Accepts two dates and returns the number of weekdays between them
  11. ' Note that this function has been modified to account for holidays. It requires a table
  12. ' named tblHolidays with a field named HolidayDate.
  13. '....................................................................
  14. On Error GoTo Err_WorkingDays2
  15. Dim intCount As Integer
  16. Dim rst As DAO.Recordset
  17. Dim DB As DAO.Database
  18.     Set DB = CurrentDb
  19.     Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
  20.  
  21.     'StartDate = StartDate + 1
  22.     'To count StartDate as the 1st day comment out the line above
  23.     intCount = 0
  24.  
  25.     Do While StartDate <= EndDate
  26.         rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
  27.         If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
  28.         If rst.NoMatch Then
  29.             intCount = intCount + 1
  30.         End If
  31.         StartDate = StartDate + 1
  32.     Loop
  33.  
  34.     WorkingDays2 = intCount
  35. Exit_WorkingDays2:
  36.     Exit Function
  37. Err_WorkingDays2:
  38.     Select Case Err
  39.     Case Else
  40.         MsgBox Err.Description
  41.     Resume Exit_WorkingDays2
  42.     End Select
  43. End Function
  44.  
Jun 13 '07 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,186
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.