Discussion Locked

Calculate 1st working day of the month

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Does anyone know how to calculate the first working day of the month in Access? I need to exclude holidays as well as weekends.

Can I link to outlook to get holiday information?

5 replies

by john_wills

Assuming you have a table of Holidays,...
Answer=FirstNotHoliday(DateValue(Year & "." & Month & ".01"))
...
private function FirstNotHoliday(StartDate AS Date) AS Date
FirstNotHoliday=
Select Case Weekday(StartDate)
Case 1
FirstNotHoliday(DateAdd("d",1,StartDate))
Case 7
FirstNotHoliday(DateAdd("d",2,StartDate)
Else
if isnull(DLookUp("[HolidayDate]","Holidays","[HolidayDate]=DateValue('" & StartDate & "')"
then
StartDate
else
FirstNotHoliday(DateAdd("d",1,StartDate))
end if
end select
end function

by munrrob

Got around problem with this code:

Dim fdDay As Date

fdDay = Weekday(fdDate) 'fdDate is passed to the function

If fdDay = vbSaturday Then
fdDay = DateAdd("d", 2, fdDate) ' Add 2 to get to Monday
ElseIf fdDay = vbSunday Then
fdDay= DateAdd("d", 1, fdDate) ' Add 1 to get to Monday
End If

' Now check that the date is not a holiday
sql = "SELECT * FROM slkpHolidays;"
rsIn = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

Do Until rsIn.EOF ' Loop until we have checked the whole table
If fdDate = rsIn![holDate] Then
fdDate = DateAdd("d", 1, fdDate) ' Increment the day by 1 until we get a valid working day
If fdDay = vbSaturday Then ' Check the new date is not a Saturday or Sunday fdDay = DateAdd("d", 2, fdDate) ' Add 2 to get to Monday
ElseIf fdDay = vbSunday Then
fdDay = DateAdd("d", 1, fdDate) ' Add 1 to get to Monday
End If
End If
Loop

This discussion is closed to new replies.