Subtract WorkDays

[parls]

Here's what I'm trying to do. I have an enddate and a number of business days
that I want to subtract from that enddate. I want to take into account
weekends and holidays when calculating. I've read many of the posts here and
tried to make use of the WorkDay math functions at
http://www.mvps.org/access/datetime/date0012.htm but I just don't have much
experience with custom functions and am not sure how to make them work.

I've copied and pasted the PreviousWorkday, SkipHolidays & IsWeekend
functions into modules. As well as the SubtractWorkDays function posted here
by Steve Clark. I have a Holidays table with US holidays that don't fall on
the weekend in it.

I have an enddate field and a buffer field (which is my # of business days I
want to subtract). Now what do I do with all these functions and how do I use
them???

Thanks for any help!

 

[parls]

Found one singular function that does it all and uses my Holidays table.

http://www.datastrat.com/Code/GetBusinessDay.txt

Option Compare Database

Public Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application
provided author
' is given credit. This code may not be distributed as part of a collection
' without prior written permission. This header must remain intact.


Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

If intDayAdd > 0 Then
Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop

ElseIf intDayAdd < 0 Then

Do While intDayAdd < 0
datStart = datStart - 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd + 1
End If
Loop

End If

GetBusinessDay = datStart

Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function

 

[Arvin Meyer [MVP]]

You found it. By the dates, you can see how long it's been working. One of
these days, I'll finish and publish the code I'm working on that finds all
the holidays in any given year, (US holidays) and automatically fills that
table. Then you'll only need to reduce the holidays by what you need to.
Specifically, bank holidays are often different than business holidays,.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

Found one singular function that does it all and uses my Holidays table.

http://www.datastrat.com/Code/GetBusinessDay.txt

Option Compare Database

Public Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application
provided author
' is given credit. This code may not be distributed as part of a
collection
' without prior written permission. This header must remain intact.


Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

If intDayAdd > 0 Then
Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <>
vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop

ElseIf intDayAdd < 0 Then

Do While intDayAdd < 0
datStart = datStart - 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <>
vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd + 1
End If
Loop

End If

GetBusinessDay = datStart

Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function

Here's what I'm trying to do. I have an enddate and a number of business
days
that I want to subtract from that enddate. I want to take into account
weekends and holidays when calculating. I've read many of the posts here
and
tried to make use of the WorkDay math functions at
http://www.mvps.org/access/datetime/date0012.htm but I just don't have
much
experience with custom functions and am not sure how to make them work.

I've copied and pasted the PreviousWorkday, SkipHolidays & IsWeekend
functions into modules. As well as the SubtractWorkDays function posted
here
by Steve Clark. I have a Holidays table with US holidays that don't fall
on
the weekend in it.

I have an enddate field and a buffer field (which is my # of business
days I
want to subtract). Now what do I do with all these functions and how do I
use
them???

Thanks for any help!

 

[John W. Vinson]

I'll finish and publish the code I'm working on that finds all
the holidays in any given year, (US holidays)

Probably futile, Arvin - different states observe different holidays, and even
within a state businesses will observe their own set.

 

[Arvin Meyer [MVP]]

I would agree, except the code uses somewhat similar principles, i.e.
LastThursday, or SecondSunday, etc. so I think that the generic parts of it
may be useful for those who want to roll their own set of holidays.. What I
plan on doing is to put in every conceivable holiday, then go to the table
and delete the ones that aren't necessary.

The alternative, is what most folks, including myself, do now. That is open
Outlook and spend and hour or so playing trying to get it to show the
holidays.