Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Other topics FAQ

Module Stuff

Calculate working days between two dates by MichaelRed
Posted: 19 Oct 00 (Edited 25 Feb 11)

The calculation of the number of working days between two given dates requires the exclusion of both holidays and 'weekend' days.  Weekends are assumed to be trditional Saturday and Sunday, however Holidays need to be explicitly defined and removed from the working days between the dates.  Further, some Holidays fall on WeekEnd days, and then need to NOT be removed from the count.  One soloution is to create the holiday list (as a table), then do a simple loop from the starting date to the ending date, accumulating (as workdays) all of the dates which are not either weekend days or hollidays.  This approach is shown below, with a 'sample' of the Holiday table.  Please also note this routine was originally generated in MS Access '97 and thus assumes DAO recordsets.  Users with later versions need to either specifically add a reference to the DAO library (Code pane, Menu, Tools, References) or convert the dbaccess code to use their recordset (Thanks to mstrmage1768 (a.k.a Robert L. Johnson III) 2/18/04. Further thanks to dhookom to change the date conversion to Integer (vs. lONG) at the top of the loop

HoliDate    HoliName
1/1/00            New Year's Day
1/17/00            Martin Luther King Day
2/2/00            Groundhog Day
2/12/00            Lincon's Birthday
2/14/00            Valentine's Day
2/21/00            President's Day
2/22/00            Washington's Birthday
3/8/00            Ash Wednesday
3/17/00            St. Patrick's Day
4/1/00            April Fool's Day
4/20/00            Passover
4/21/00            Good Friday
5/5/00            Cinco de Mayo
5/14/00            Mother's Day
6/11/00            Pentecost
6/18/00            Father's Day
7/4/00            Independence Day
9/4/00            Labor Day
10/31/00    Halloween
11/11/00    Vetran's Day
11/23/00    Thanksgiving
12/25/00    Christmas
12/31/00    New Year's Eve

First, Get the number of days between the dates
NumDays: DeltaDays([StDt], [EndDt])

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = Int(StartDate) To Int(EndDate)
        Select Case (WeekDay(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
               rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function


Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close