Previous Business Day

[Jen]

I found the following code for determing Previous Business Day on this site.
When I test is using "12/29/09" as the BgnDate, it returns "12/29/09" when it
should be returning "12/28/09".

Any thoughts?

Public Function PreviousBD(BgnDate As Date) As Date

Dim rsHolidays As DAO.Recordset
Dim bdNum As Integer
Dim isHoliday As Boolean
Dim strSQL As String

strSQL = "Select Holiday from tblBCBSMN_Holidays"
Set rsHolidays = CurrentDb.OpenRecordset(strSQL)

Do
bdNum = Weekday(BgnDate)
'determine which day the date is,
'then calc previous BD
Select Case bdNum
'Tuesday to Saturday
Case 3 - 7
BgnDate = BgnDate - 1
'Sunday
Case 1
BgnDate = BgnDate - 2
'Monday
Case 2
BgnDate = BgnDate - 3
End Select

' now check if BgnDate is a holiday
'search the recordset
rsHolidays.FindFirst "[Holiday] = #" & BgnDate & "#"
If rsHolidays.NoMatch Then
Exit Do
Else
BgnDate = BgnDate - 1
'check once more for back to back holidays
rsHolidays.FindFirst "[Holiday] = #" & BgnDate & "#"
If rsHolidays.NoMatch Then 'True = no records
Exit Do
Else
BgnDate = BgnDate - 1
End If
End If
Loop

'clean up
rsHolidays.Close
Set rsHolidays = Nothing

'return Previous Business Day
PreviousBD = BgnDate

End Function

Thanks!

 

[klen_]

Try this code.

Private Sub Button0_Click()
	PreviousBD #12/29/2009#
End Sub

Public Function PreviousBD([b]ByVal[/b] BgnDate As Date) As Date
Dim rsHolidays As DAO.Recordset
Dim bdNum As Integer
Dim isHoliday As Boolean
Dim strSQL As String
strSQL = "Select Holiday from tblBCBSMN_Holidays"
Set rsHolidays = CurrentDb.OpenRecordset(strSQL)
Do
	bdNum = Weekday(BgnDate)	'determine which day the date is, then calc previous BD
 
	Select Case bdNum
	[b]Case 3 To 7 'Tuesday to Saturday[/b]
		BgnDate = BgnDate - 1
	Case 1	 'Sunday
		BgnDate = BgnDate - 2
	Case 2	 'Monday
		BgnDate = BgnDate - 3
	End Select
 
	' now check if BgnDate is a holiday search the recordset
	[b]rsHolidays.FindFirst "[Holiday]=#" & Format(BgnDate, "mm\/dd\/yy") & "#"[/b]
 
	If rsHolidays.NoMatch Then
		Exit Do
	Else
		BgnDate = BgnDate - 1
		'check once more for back to back holidays
		[b]rsHolidays.FindFirst "[Holiday]=#" & Format(BgnDate, "mm\/dd\/yy") & "#"[/b]
		If rsHolidays.NoMatch Then 'True = no records
			Exit Do
		Else
			BgnDate = BgnDate - 1
		End If
	End If
Loop
'clean up
rsHolidays.Close
Set rsHolidays = Nothing
'return Previous Business Day
PreviousBD = BgnDate 
End Function

 

[Michael_0097]

'This code is the same as above (Klen), but improved and tested to work)
'the down side to this code is that you need to keep a table updated with holiday, dates - upside it's more flexible
'previous code did not re-check for a weekend date, this one will
'1st create a table called [T_holiday] with one field called [Holiday], data type=date
'Add in the holidays for the year you are testing
'add mulitiple holidays dates next to each other and this code should work
'add below to immediate window to test
'debug.print PreviousBD (#7/5/2013#)
'change year as needed i.e. 2013
Public Function PreviousBD(ByVal BgnDate As Date) As Date
On Error GoTo eh
Dim rsHolidays As DAO.Recordset 'Add reference to Microsoft DAO N.N Object Library
Dim bdNum As Integer
Dim isHoliday As Boolean
Dim strSQL As String
strSQL = "Select Holiday from T_holiday"
Set rsHolidays = CurrentDb.OpenRecordset(strSQL)
Do
bdNum = Weekday(BgnDate) 'determine which day the date is, then calc previous BD

Select Case bdNum
Case 3 To 7 'Tuesday to Saturday
BgnDate = BgnDate - 1
Case 1 'Sunday
BgnDate = BgnDate - 2
Case 2 'Monday
BgnDate = BgnDate - 3
End Select

' now check if BgnDate is a holiday search the recordset
rsHolidays.FindFirst "[Holiday]=#" & Format(BgnDate, "mm\/dd\/yy") & "#"

If rsHolidays.NoMatch Then
Exit Do
Else
BgnDate = BgnDate - 1
'check for back to back holidays
rsHolidays.FindFirst "[Holiday]=#" & Format(BgnDate, "mm\/dd\/yy") & "#"
If rsHolidays.NoMatch Then 'True = no records
'do nothing
Else
BgnDate = BgnDate - 1
End If
End If
bdNum = Weekday(BgnDate) 'determine which day the date is, then calc previous BD
Loop Until bdNum > 1 And bdNum < 8 'loop until we find a non-weekend date
ex: 'clean up
rsHolidays.Close
Set rsHolidays = Nothing
PreviousBD = BgnDate 'return Previous Business Day
Exit Function
eh:
MsgBox Err.Description & " " & Err.Number
GoTo ex
End Function