Calculate 1st working day of the month
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?
Web Development
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?
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
by munrrob
This discussion is closed to new replies.
Sort by Trending