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

Working with work days

P: 55

I'm working in Access 2000. I have a form with a series of date fields, showing the progress of a project from start to completion. There's a set of fields/controls for projected dates (when the project is projected to enter each stage of the process) and another set for actual dates. For my purposes, I'm only working with the projected dates.

There is a formula, based on the size of the project (how many hours it will take), for how many days each step of the process will require. I'm trying to set up the code so that when the projected completion date is entered, and based on the number of hours that has been entered, all the other projected dates will fill in automatically.

So in other words, if someone enters a project that will take 525 hours with a projected completion date (CompletionPJ) of 6/25/09, the database will automatically fill in the projected start date as [CompletionPJ] - 60 (and will fill in all the other projected dates in the middle according to their respective equations).

I've figured out the easy part of this (code is below), but I'm trying to figure out how to get it to count only work days. For instance, in the formula above, it would be [CompletionPJ] - [60 work days, not counting weekends or holidays].

I have a separate calendar table with CalendarID (autonumber), CalendarDate, CalendarDate2, isWorkDay, and isHoliday. (CalendarDate and CalendarDate2 are identical date fields; they make it possible for me to find the number of working days between two dates for other reports in the database.) isWorkDay and isHoliday are yes/no (all weekend dates are false in isWorkDay; weekday holidays are marked true in isHoliday). So I'm trying to figure out how to tell Access to subtract from [completionPJ] a certain number of days where isWorkDay = True and isHoliday = False.

At this point I don't have the Calendar table included in the query for this form, but I'm guessing I'll need to add it (unless it's possible to make VB code check info in a table that's not part of the data source for the form??).

Here's the code I have so far:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCompletionPJ_AfterUpdate()
  3. If Not IsNull(Me![txtHours]) Then
  5.     Select Case txtHours
  7.     Case 1 To 499
  8.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 30
  10.     Case 500 To 999
  11.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 60
  13.     Case 1000 To 1499
  14.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 90
  16.     Case 1500 To 1999
  17.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 120
  19.     Case Is >= 2000
  20.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 180
  22.     End Select
  24. End If
  26. End Sub
In all there are about 12 steps in the process, so there will actually be a dozen lines for each case.

Anybody have an idea to help me out?

Nov 12 '08 #1
Share this Question
Share on Google+
2 Replies

P: 68
Hi, there are a few examples of workdays functions around if you google ms access workdays function, you could also look at [HTML][/HTML]

Also here's a couple of functions that I've borrowed and/or reworked a few times. From what I remember I had problems with the first one, where I couldn't get it to infallibly return the correct number of days (when compared against manual counts), but I use the second one regularly without probs. I know I adapted this from one I saw elsewhere, until it worked for me in my particular context so you may also need to fiddle with it.

One thing, when I first tried to solve this same issue I spent hours and hours fighting with the original functions trying to get them to work properly - the major problem (I subsequently realised) was that I needed to control for nulls within the query formula as well as or instead of from within the formula, otherwise it just locked up and hung. Just a tip that might save you some pain!


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
  5. 'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
  6. 'This function calculates the number of workdays between two dates.
  7. 'The number of workdays is inclusive of the beginning and ending dates.
  8. 'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
  9. 'The table must list the dates of holidays to be observed.
  10. 'The user may include other fields in that table, for example, a description of the holiday being observed.
  12. Dim lngTotalDays As Long
  13. Dim lngTotalWeeks As Long
  14. Dim dtNominalEndDay As Date
  15. Dim lngTotalHolidays As Long
  17. 'Check to see if dtStartDay > dtEndDay.  If so, then switch the dates
  18. If dtStartDay > dtEndDay Then
  19.     dtNominalEndDay = dtStartDay
  20.     dtStartDay = dtEndDay
  21.     dtEndDay = dtNominalEndDay
  22. End If
  23. 'Here are how many weeks are between the two dates
  24. lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
  25. 'Here are the number of weekdays in that total week
  26. lngTotalDays = lngTotalWeeks * 5
  27. 'Here is the date that is at the end of that many weeks
  28. dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
  29. 'Now add the number of weekdays between the nominal end day and the actual end day
  30. While dtNominalEndDay <= dtEndDay
  31.     If WeekDay(dtNominalEndDay) <> 1 Then
  32.         If WeekDay(dtNominalEndDay) <> 7 Then
  33.             lngTotalDays = lngTotalDays + 1
  34.         End If
  35.     End If
  36.     dtNominalEndDay = dtNominalEndDay + 1
  37. Wend
  38. 'Here are how many holiday days there are between the two days
  39. lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= #" & dtEndDay & "#  AND dtObservedDate >= #" & dtStartDay & "# AND Weekday(dtObservedDate) <> 1 AND Weekday(dtObservedDate) <> 7")
  41. 'Here are how many total days are between the two dates - this is inclusive of the start and end date
  42. funWorkDaysDifference = lngTotalDays - lngTotalHolidays
  44. End Function

Expand|Select|Wrap|Line Numbers
  1. Public Function fWorkDays(StartDate As Variant, EndDate As Variant) As Variant
  2. 'Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
  3. On Error GoTo Err_fWorkDays
  5. Dim intCount As Integer
  6. Dim rst As DAO.Recordset
  7. Dim DB As DAO.Database
  9. Set DB = CurrentDb
  10. Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
  12. 'StartDate = StartDate + 1
  13. 'To count StartDate as the 1st day comment out the line above
  15. intCount = 0
  17. Do While StartDate <= EndDate
  19. If Not IsNull(StartDate) And Not IsNull(EndDate) Then
  20.     rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
  21.         If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
  22.             If rst.NoMatch Then
  23.             intCount = intCount + 1
  24.             End If
  25.         End If
  26. End If
  28. StartDate = StartDate + 1
  30. Loop
  32. fWorkDays = intCount
  34. Exit_fWorkDays:
  35. Exit Function
  37. Err_fWorkDays:
  38. Select Case Err
  40. Case Else
  41. MsgBox Err.Description
  42. Resume Exit_fWorkDays
  43. End Select
  45. End Function
Nov 12 '08 #2

P: 55
Thanks for the ideas. I googled and found a function that does work in theory, but now I'm trying to figure out how to connect this function/module to the code in my form. I haven't worked with functions a whole lot, so I'm still figuring this out.

Here's the function (based on

Expand|Select|Wrap|Line Numbers
  1. Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
  2. Dim lngAdd As Long
  3. Dim lngDayCount As Long
  5. On Error GoTo AddWorkDays_Error
  7. If DaysToAdd < 0 Then
  8.     lngAdd = -1
  9. Else
  10.     lngAdd = 1
  11. End If
  13. AddWorkDays = OriginalDate
  15. Do Until lngDayCount = DaysToAdd
  16. AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
  17. If Weekday(AddWorkDays, vbMonday) < 6 Then
  18.     If IsNull(DLookup("[holidate]", "HolidaysTable", "[holidate] = #" & _
  19.         AddWorkDays & "#")) Then
  20.         lngDayCount = lngDayCount + lngAdd
  21.     End If
  22. End If
  23. Loop
  25. AddWorkDays_Exit:
  26. On Error GoTo 0
  27.     Exit Function
  29. AddWorkDays_Error:
  30.     MsgBox "Error " & Err.Number & " (" & Err.Description & _
  31.         ") in procedure AddWorkDays of Module modDateFunctions", vbExclamation, conMsgTitle
  32.     GoTo AddWorkDays_Exit
  34. End Function
In my form, txtCompletionPJ corresponds to OriginalDate in the function, and for each date (such as txtStartPJ or txt2ndStepPJ) in each Case I have to specify a certain (negative) number to correspond to DaysToAdd in the function. The end result of the function, AddWorkDays, corresponds to the date I'm trying to find (such as txtStartPJ or txt2ndStepPJ).

Here's another example of the code in my form (two cases with two controls each for now). Can you give me any guidance on how I connect the function to the form? I've tried but I haven't figured it out.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub txtCompletionPJ_AfterUpdate()
  6. If Not IsNull(Me![txtHours]) Then
  7.     Select Case txtHours
  8.     Case 1 To 499
  9.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 30
  10.         Me!txt2ndStepPJ.Value = Me!txtCompletionPJ - 25
  11.      Case 500 To 999
  12.         Me!txtStartPJ.Value = Me!txtCompletionPJ - 60
  13.         Me!txt2ndStepPJ.Value = Me!txtCompletionPJ - 53
  14.     End Select
  15. End If
  17. End Sub
Nov 14 '08 #3

Post your reply

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