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!
  • Students Click Here

*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 Forms FAQ

How to

Find the working days between Dates by Rolliee
Posted: 16 Feb 04

The following code in a global module will find the number of working days between two dates. Only Mondays thry Fridays are counted. If there is a table named "Holidays" the function opens it once and makes a public array of holidays. The first field must hold the dates.  If no table is provided, only Christmas, The Fourth, New Years and MY Birthday are listed <G>

The form is

me.txtNumDays = GetDay( me.date1, me.date2)

Rollie E

Public HDi(12) As Date, iHDi As Integer

Public Function GetDay(ByVal d1 As Date, ByVal d2 As Date) As Long
Dim rs As DAO.Recordset, Numof As Long, i As Integer
Numof = 0

  Do While d2 > d1
     If good1(d2) Then Numof = Numof + 1
     d2 = d2 - 1
GetDay = Numof
If iHDi = 0 Then
    On Error GoTo tellme:
    Set rs = CurrentDb.OpenRecordset("holidays")
    rs.MoveLast: rs.MoveFirst
    Do While Not rs.EOF
      HDi(i) = rs.Fields(0)
      iHDi = iHDi + 1
    GoTo around:
     If Err.Number = 3078 Then
         HDi(0) = CDate("12/25/2004")
         HDi(1) = CDate("7/4/2004")
         HDi(2) = CDate("1/1/2005")
         HDi(3) = CDATE("9/1/2004")
         iHDi = 4
         Exit Function
     End If
     Set rs = Nothing
 End If

End Function

Public Function good1(d2 As Date) As Boolean
Dim okay As Boolean, i As Integer

okay = False

Select Case Weekday(d2)
  Case 2, 3, 4, 5, 6
    okay = True
  Case 1, 7
    okay = False

End Select
good1 = okay
If okay Then
     For i = 0 To iHDi - 1
       If HDi(i) = d2 Then okay = False
End If

End Function

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms 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