General discussion

Locked

Calculate 1st working day of the month

By munrrob ·
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.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Calculate 1st working day of the month

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

Here's some code:

http://tinyurl.com/95ad

It's a lot easier to do in Excel.

Collapse -

Calculate 1st working day of the month

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

Poster rated this answer

Collapse -

Calculate 1st working day of the month

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

Collapse -

Calculate 1st working day of the month

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

Collapse -

Calculate 1st working day of the month

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

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums