Web Development

General discussion

Locked

Calculate 1st working day of the month

By ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

Calculate 1st working day of the month

by In reply to Calculate 1st working day ...

Here's some code:

It's a lot easier to do in Excel.

Collapse -

Calculate 1st working day of the month

by In reply to Calculate 1st working day ...

Collapse -

Calculate 1st working day of the month

by In reply to Calculate 1st working day ...

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
Case 7
Else
if isnull(DLookUp("[HolidayDate]","Holidays","[HolidayDate]=DateValue('" & StartDate & "')"
then
StartDate
else
end if
end select
end function

Collapse -

Calculate 1st working day of the month

by In reply to Calculate 1st working day ...

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
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

Collapse -

Calculate 1st working day of the month

by In reply to Calculate 1st working day ...

This question was closed by the author

Related Discussions

• 12

• 23

• 3

How can I get high quality backlinks and improve my website rank?

Affiliatelance ·

• 4

-Downing ·

• 4

PattyTech ·