Modify an entered date to the following business day

Ease of Use


Version tested with


Submitted by:



This is an Access function that automatically adjusts a user-entered date to the next working day. It takes into account holidays entered into a holidays table and adjusts accordingly. 


Let's say you have a form for a project. The start date & end date must be a working day & if the day the user enters is either a weekend date or a holiday, you want the date to adjust to the next working day. 


instructions for use


Option Compare Database Option Explicit Public Function fModBusDay(ByVal dDay As Date) As Date 'Returns days to add to specified date depending 'if day selected is a weekend or exists in tbl_holidays Dim stSQL As String Dim rst As ADODB.Recordset Dim lAdd As Long Dim dHol As Date 'first we get to the monday if dDay is Sat or Sun TestWeekDay: Select Case Weekday(dDay, vbMonday) Case 1 To 5: lAdd = 0 Case Is = 6: lAdd = 2 Case Is = 7: lAdd = 1 End Select dDay = DateAdd("d", lAdd, dDay) 'then we check to see if the revised date is a holiday stSQL = "SELECT HolDate FROM tbl_Holidays WHERE HolDate = #" & dDay & "#" Set rst = CurrentProject.Connection.Execute(stSQL, , adCmdText) 'i.e. if a record is returned If Not rst.BOF Then dHol = rst(0) rst.Close End If 'then check if dday is a holiday If dHol = dDay Then 'if so add another day and re-test dDay = DateAdd("d", 1, dDay) GoTo TestWeekDay Else ' if not, we have a valid day & can exit fModBusDay = dDay GoTo ExitHere End If ExitHere: End Function

How to use:

  1. Download the sample file
  2. Open the form fm_project
  3. Enter data
  4. To add holiday date to the database, open tbl_holdays & add further dates

Test the code:

  1. Entering a date that is a Saturday, a Sunday, or a Holiday Date shoudl prompt the data to be adjusted accordingly to the following available business day.

Sample File: 29.34KB 

Approved by mdmackillop

This entry has been viewed 106 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express