BizDay From X Day Calculation

[JPeters]

Hi

Trying to write a function to return only biz days from a given date (M-F). I want 4 biz days ago (backwards) from a given date, usually Today's date. I need it to work longer than a period of one week because I need the code to check 4th biz day for the past month or so (backwards). Today is Tuesday, I need it to check 4 biz days ago - so check Wednesday the 4th, also check 8 days ago - Thurs the 29th of July... etc.

I've began writing some code to return a Date function based on a passed in date and a number specified, but I'm having trouble wracking my brain to figure out what to do when I send it something above 5 days, like lstBizDate(Date, 8) ... I can't quite remember from my old college coding classes how to realize that there were 2 weekends between now and then ... DateDiff()? .. and realize what day to return.

I've taken some code from this thread: How can i calculate business days? thread181-47925. It has some serious processing errors. It's decent, but I absolutely cannot have this processing holidays, so I have removed the holiday check from the code. It appears to work in some cases, however... a lot of the time it is not functional and it gives me a few days off. I am having trouble following the code, which is relatively rare for me, but I've been looking at it for so long that I could really use an outside perspective.

Please look to the thread mentioned for Michael's code... try to fix it. It really has problems. Just get rid of the recordset and query defs in his code, and everything to do with the Holidays table. Post it into your own DB and test away.

Here's what I'v written sofar if anyone wants to just simplify it and help me finish it up. I could use a push in the right direction. It's incomplete, I've just started writing it, but I can't even think of the algorithm I need to use..

Public Function lstBizDay(dt As Date, pstDys As Integer) As Date

'Written 10/10/04
'Designed to check the value of a passed in date and subtract
'the passed in business days.

Dim tmpDate As Date
tmpDate = dt - pstDys

If Weekday(tmpDate) = 7 Or Weekday(tmpDate) = 1 Then 'day is Sat or Sun
MsgBox (Weekday(tmpDate))
tmpDate = tmpDate - 2
lstBizDay = tmpDate
Exit Function
End If
lstBizDay = tmpDate

End Function

------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[JPeters]

I scratched that completely. I'm writing a new function that checks for every day inbetween my start date and end date... see's if it's a weekend. Then subtracts 2 days or adds 2 days per weekend. I think in theory that this works.
If any of you ahve already written this and want to save me the trouble, please let me know. THanks.

-Josh

------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[JPeters]

Here's what I came up with. It works like a charm.

Public Function lstBizDay(dt As Date, dys As Integer) As Date

'Written by Joshua Peters on 10/10/04
'Designed to check the value of a passed in date and subtract
'the passed in business days. This does not account for holidays on purpose, but that can be easily added.
'This function is being called by the qry_po_receiving_out query, based off of the PO Receiving form.
'The query was rewritten for this code.
'To add for forward biz day progression, simply create a boolean parameter to true for forward
'and do additions instead of subtractions based on that boolean. Or even better, add a multiplier
'so we only need to feed a negative or positive integer. Do this at a later date.

Dim endDate As Date
Dim tmpDate As Date
Dim intDys As Integer
Dim aCounter As Integer

aCounter = 0
intDys = dys
tmpDate = dt

'For Days Backwards - Counts Number of weekends
Do While aCounter < intDys
tmpDate = tmpDate - 1
If Weekday(tmpDate) = 7 Then 'Sunday is hit, school's out for the weekend!
intDys = intDys + 2 'Adds 2 days to count for weekend
End If
aCounter = aCounter + 1
Loop

endDate = dt - intDys
lstBizDay = endDate

End Function
Private Function Tester()
MsgBox (lstBizDay(Date, 15))
End Function

------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[MichaelRed]

Just curious, but could you post the specifics re the 'serious problems' in the ref thread?





MichaelRed
mlred@verizon.net

 

[MichaelRed]

Also, you might look at faq705-3213





MichaelRed
mlred@verizon.net