Help on Business Days

[bng]

How can I identify records that are 5 Business Days old? Currently, records imported daily from a .txt file are defaulting to Date()

Thank you in advance
BNG

 

[raskew]

You've presented us with two different issues:

(1) Correctly interpreting the textfile date. For this, you'll need to provide more information (and examples).
(2) Manipulating business days (weekdays). Hopefully the following function will resolve that part of the problem:

Function SubtractWkDays(vardate As Variant, numdays As Integer, incl As Boolean)
'*******************************************
'Name: SubtractWkDays (Function)
'Purpose: Simple, non-formula method of
' subtracting weekdays from a given
' date
'Inputs: ? subtractwkdays("10/6/00", 300, false) --or--
' ? subtractwkdays(#10/6/00#, 300, false)
'Output: 8/15/1999
'Note: if incl = true then include vardate in the
' calculation, otherwise don't
'*******************************************
Dim thedate As Date, n As Integer

thedate = DateValue(vardate)
n = numdays
Do While n > 0
If WeekDay(thedate, 1) >= 2 And WeekDay(thedate, 1) <= 6 Then
n = n - 1
End If
thedate = thedate - 1
Loop
SubtractWkDays = thedate + IIf(incl = True, 1, 0)

End Function

 

[MichaelRed]

bng,

raskew's method only gives the number of WEEKDAYS in the interval. There is a function listed in one of the Ms. Access FAQ,s which includes the holidays between the dates as well. Go to the FAQ, type &quot;workdays&quot; in the subject matter, select all forums (I don't rember where this was posted - BUT it was Ms Access). I'm sure you will see the topic in one of the Ms. Access FAQ listings. Go to that FAQ/Thread.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over