If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subtract WorkDays
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! |
#2
|
|||
|
|||
Nevermind - Subtract WorkDays
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_He rst.Close Set rst = Nothing Set DB = Nothing Exit Function Error_Handler: MsgBox Err.Number & ": " & Err.Description Resume Exit_Here End Function "parls" wrote: 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! |
#3
|
|||
|
|||
Nevermind - Subtract WorkDays
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 "parls" wrote in message ... 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_He rst.Close Set rst = Nothing Set DB = Nothing Exit Function Error_Handler: MsgBox Err.Number & ": " & Err.Description Resume Exit_Here End Function "parls" wrote: 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! |
#4
|
|||
|
|||
Nevermind - Subtract WorkDays
On Wed, 28 Apr 2010 15:06:09 -0400, "Arvin Meyer [MVP]"
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Nevermind - Subtract WorkDays
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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "John W. Vinson" wrote in message ... On Wed, 28 Apr 2010 15:06:09 -0400, "Arvin Meyer [MVP]" wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|