Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16

    Exclamation Unanswered: Calculating Networkdays

    I am trying to calculate the networkdays between two dates in one table.
    Project Start Date and Project Ship Date. I can export into excel and use the formula =networkdays (Date1, Date2, Holidays) but i would like to do it on a form. The best i got done so far was to calculate the days in between without considering weekends and holidays.

  2. #2
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16
    does anyone have any idea?

  3. #3
    Join Date
    Feb 2004
    Posts
    142
    Create a public function and pass in the two dates. You can run a loop and counter to loop through the dates between the provided dates, check the day of week and increment the loop on the weekdays you want.

    To check for holidays you will either need a table of holidays you do not want to count or hard code it into the function. Doing a table is a better way to do it as the holidays can be changed easily without going back into the code.

    Then have the function return the counted days.
    KC

  4. #4
    Join Date
    Mar 2004
    Location
    PA
    Posts
    16
    Wow i have no idea what you mean.
    Any easy way to explain this i am not that good with access.
    What is a public function and a loop.
    I see what you mean about the table.
    But other then that i am worthless you could say.

  5. #5
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    If you need to store the calculated value, use a query, and make a field like: [NetworkDays]: NetworkDays([Date1],[Date2],[Holidays]). If you want just to show it on a form, create an unbound text box control, and set its controlsource property to =NetworkDays([Date1],[Date2],[Holidays]). Should work not worse than on a spreadsheet
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  6. #6
    Join Date
    Feb 2004
    Posts
    142
    To create this function first create a table called tblHolidays

    In this table have two fields a date field called HolidayDate and another field called HolidayDesc enter the holiday dates and a description foe each holiday.

    Then goto the modules tab in the database window and create a new module and paste the code below into it.

    To call the function as a calculated testbox
    ControlSource = calcWorkDays([txtStartDate],[txtEndDate])

    In a query
    [WorkingDays]: calcWorkDays([FieldStartDate],[FieldEndDate])

    Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
    Dim i As Long 'day counter
    Dim dteCurDay As Date
    'set i = 1 if you want the first date to count as a full day
    'or i = 0 if you do not want the first day to count as a full day

    i = 0
    dteCurDay = dteStart
    Do Until dteCurDay >= dteEnd
    'check date against holiday table
    If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
    'continue checking for weekdays
    If WeekDay(dteCurDay, vbSunday) <> vbSunday And _
    WeekDay(dteCurDay, vbSunday) <> vbSaturday Then
    i = i + 1
    End If
    End If
    dteCurDay = DateAdd("d", 1, dteCurDay)
    Loop

    End Function
    KC

  7. #7
    Join Date
    Feb 2004
    Posts
    142
    I forgot the return statement
    just above the end function line add this

    calcWorkDays = 1
    KC

  8. #8
    Join Date
    Feb 2004
    Posts
    142
    CalcWorkDays = i
    KC

  9. #9
    Join Date
    Mar 2010
    Posts
    2
    and there's a line that needs correcting to make it work.
    WeekDay(dteCurDay, vbSunday) <> vbSaturday Then

    needs to be changed to

    WeekDay(dteCurDay, vbSaturday) <> vbSaturday Then

    Seriously, I'm really glad I found this post - it's been very helpful! Thanks!

  10. #10
    Join Date
    Mar 2010
    Posts
    2
    Actually, that was wrong too. I've changed things a bit so that it's now working.

    Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
    Dim i As Long 'day counter
    Dim dteCurDay As Date
    'set i = 1 if you want the first date to count as a full day
    'or i = 0 if you do not want the first day to count as a full day

    i = 1
    dteCurDay = dteStart
    Do Until dteCurDay >= dteEnd
    'check date against holiday table
    If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
    'continue checking for weekdays i.e. i increases only if week day value is not 1(Sunday) and not 7(Saturday)
    If Weekday(dteCurDay) <> 1 And _
    Weekday(dteCurDay) <> 7 Then

    i = i + 1
    End If
    End If
    dteCurDay = DateAdd("d", 1, dteCurDay)
    Loop

    calcWorkDays = i
    'based on http://www.dbforums.com/microsoft-ac...tworkdays.html
    End Function

  11. #11
    Join Date
    Nov 2010
    Posts
    1
    May I know what to change because I have not been able to working

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •