Date()-2 (exclude weekends)

  • Thread starter Thread starter dchristo
  • Start date Start date
D

dchristo

I need to get info from 2 days prior and need to exclude weekends

ex: today is August 12, 2008 - I need the info from August 8, 2008.

Any help would be appreciated.
 
dchristo said:
I need to get info from 2 days prior and need to exclude weekends

ex: today is August 12, 2008 - I need the info from August 8, 2008.

Any help would be appreciated.
Click to expand...


To do this, you need to use VBA code to calculate the date based on weekdays
vs. weekends. Here's a link to a set of "workday math" functions:

http://www.mvps.org/access/datetime/date0012.htm
Doing WorkDay Math in VBA
 
dchristo,
Chcek out the Weekday function in Help.
Use that function to determine what weekday is today's date, and
build an IF statement or Case Select to generate the different outcomes.
In aircode...
If Weekday(Date()) is Monday subtract 3 days
ElseIf Weekday(Date()) is Tuesday subtract 4 days
Else subtract 2 days.

If you might run this on a Saturday or Sunday, you'll need to add that
logic to the IF statement.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
The following is a simplified function which adds or subtracts workdays:

Public Function WorkDaysAdd(dtmDateFrom As Date, intDays As Integer) As Date

Dim dtmDate As Date
Dim n As Integer
Dim intIncr As Integer

' value by which date will be incremented
' each day is 1 or -1 depending on sign of
' intDays argument
intIncr = intDays / Abs(intDays)

' initialize date from which to count
dtmDate = dtmDateFrom

' add days one by one
For n = 1 To Abs(intDays)
dtmDate = DateAdd("d", intIncr, dtmDate)
' skip weekends
Do While Weekday(dtmDate, vbMonday) > 5
dtmDate = DateAdd("d", intIncr, dtmDate)
Loop
Next n

WorkDaysAdd = dtmDate

End Function

To return the date two working days before today call it like so:

WorkDaysAdd(Date(),-2)

This simplified function does not take account of public holidays, however,
but its quite a simple task to build that in so that the code looks up rows
in a Holidays table and discounts them.

Ken Sheridan
Stafford, England
 
That worked perfectly, thank you so much!!!!!

Ken Sheridan said:
The following is a simplified function which adds or subtracts workdays:

Public Function WorkDaysAdd(dtmDateFrom As Date, intDays As Integer) As Date

Dim dtmDate As Date
Dim n As Integer
Dim intIncr As Integer

' value by which date will be incremented
' each day is 1 or -1 depending on sign of
' intDays argument
intIncr = intDays / Abs(intDays)

' initialize date from which to count
dtmDate = dtmDateFrom

' add days one by one
For n = 1 To Abs(intDays)
dtmDate = DateAdd("d", intIncr, dtmDate)
' skip weekends
Do While Weekday(dtmDate, vbMonday) > 5
dtmDate = DateAdd("d", intIncr, dtmDate)
Loop
Next n

WorkDaysAdd = dtmDate

End Function

To return the date two working days before today call it like so:

WorkDaysAdd(Date(),-2)

This simplified function does not take account of public holidays, however,
but its quite a simple task to build that in so that the code looks up rows
in a Holidays table and discounts them.

Ken Sheridan
Stafford, England
Click to expand...
 
Back
Top