Hi,
I'm trying to write a code to caluclate the working day a week before the end of the month. I've found similar codes from various places on the net and tried to use them where i can but no luck.
If the date is 7 days before end of month and its a weekend or a public holiday then i want it to minus a day until it reaches the previous working day. I created a public holiday table that stores all public holidays for the year.
Any help would be much appreciated. Thanks.
Public Function WeekBeforeEOM() As Date
Dim Searching As Boolean
Dim LastWorkday As Date
Dim i As Integer
Searching = True
LastWorkday = DateSerial(Year(Date), Month(Date) + 1, 0) '-- Start at the last day of the month
i = 1
Do While Searching
If Weekday(LastWorkday, vbMonday) <= 5 Then
If IsNull(DLookup("[HoliDate]", "tblHolidays", "[HoliDate] = " & LastWorkday)) Then
LastWorkday = LastWorkday - 7
Searching = False
Else
WeekBeforeEOM = LastWorkday - 7 - i
Searching = False
End If
End If
i = i + 1
Searching = False
Loop
End Function
I'm trying to write a code to caluclate the working day a week before the end of the month. I've found similar codes from various places on the net and tried to use them where i can but no luck.
If the date is 7 days before end of month and its a weekend or a public holiday then i want it to minus a day until it reaches the previous working day. I created a public holiday table that stores all public holidays for the year.
Any help would be much appreciated. Thanks.
Public Function WeekBeforeEOM() As Date
Dim Searching As Boolean
Dim LastWorkday As Date
Dim i As Integer
Searching = True
LastWorkday = DateSerial(Year(Date), Month(Date) + 1, 0) '-- Start at the last day of the month
i = 1
Do While Searching
If Weekday(LastWorkday, vbMonday) <= 5 Then
If IsNull(DLookup("[HoliDate]", "tblHolidays", "[HoliDate] = " & LastWorkday)) Then
LastWorkday = LastWorkday - 7
Searching = False
Else
WeekBeforeEOM = LastWorkday - 7 - i
Searching = False
End If
End If
i = i + 1
Searching = False
Loop
End Function