Work Days in Access VBA

[vspsdca]

Hello,

This is my 2nd post on this forum regarding the above subject, unfortunately I haven't really gotten the answer I have been looking for. I'm a newbie in VBA and just starting to get familiar with it so please bear with me if I may sound a little ignorant about this subject.

My scenario:

Due_date = Date_Request_Raised + SLA days (where SLA can either be 1, 5, 7, 10, 20, 30 days)

On my form I have all three in text boxes. Once the Date_Request_Raised has been entered and the SLA chosen from the dropdown box, the Due_date must be automatically populated with the date on the condition that the Due_Date will skip the weekends and holidays.

I have seen a lot of examples here but most are computing for the number of days between dates excluding wkends/holidays etc...

I'd really appreciate if you someone can be patient enough to walk me through this....

Many thanks.

 

[Uncle Gizmo]

>>>Due_Date will skip the weekends and holidays<<<
That's a very strange calculation for anyone to request, are you sure that's correct?

You see, you have to think about doing it manually, you wouldn't would you, no one is going to sit down and say that the DUE date for this is in 30 days time, but hold on I have to exclude weekends and holidays, is just not going to happen.

So I think you are misinterpreting the instructions that you have been given. Please confirm this and post back.

 

[vspsdca]

Thanks for the quick reply Uncle Gizmo.

So there is no hope to calculate the Due_date? I've thought of using a pop-up calendar for the user to use but to for them to manually count the days from a calendar and then select that date to populate the Due_date is a bit ancient considering that this is basically the reason why we have programs like VBA, SQL etc to help automate functions like this.

I dont think I have misinterpreted the instructions given to me.

 

[LPurvis]

Are you actually wanting a date (1, 5, 7, 10, 20, 30 days from now) including work days only? Or if the day which lands upon in 30 days is a weekend or holiday - to then move that day to a valid working day?

Either way - the request for a day X working days in the future or for a result day only to be working doesn't seem so very odd.
There are certainly many VBA examples of workday functions.
For example there's an ongoing thread in this very forum (here) about it.

Try those offered here too. I did one once too (it's all much of a muchness - not to say Brent's aren't good) - but it was a long time ago and don't see the point in looking for it when so many other good examples are around. lol

Cheers.

 

[Uncle Gizmo]

Actually, I was thinking of "receiving a payment" from a customer, that sort of due date, however I realize that there is a situation where the sort of calculation you want would be necessary.

Let's say you were specifying a "due date" for when a project would be finished. Then of course you would have to take into account weekends and holidays. So I think I allowed myself to be misled by the be other interpretation of "due date" when a payment is due.

 

[Uncle Gizmo]

I think (as you appear to have already discovered) that there is plenty of code out there for calculating holidays and weekends between two dates, and return the number of available working days.

However your problem is that you need to identify a date 30 days hence, (I will choose 30 as an arbitrary value for talking about the problem) BUT... It's not a fixed number of days, the number of days between them; now and then, depends on the number of weekend and holidays.

I think the approach I would use is utilize the already existing code, so let's take an example you want to find that particular date 30 days hence. You know what today is, so now you can check tomorrow, is tomorrow a holiday or a weekend?

You could find this out with the existing code examples, adapt one to return true or false depending on whether it is a holiday or not, if it's not a holiday, add one, check the next day, is it a holiday or weekend, if not add one, carry on like this until you reach 30, and that particular day will be your answer.

 

[vspsdca]

Thanks LPurvis and Uncle Giz

Btw LP, the first link you gave is not working.

To answer your question, Yes - I actually want a date generated based on e.g., today's date plus 1, 5, 7, 10, 20, 30 days from now. For instance I received a request today and it will take me at least 7days to complete the request, I want my due date box to be populated with the date of 07/05/2009 (this exludes the weekend and the Bank holiday on May 4th).

Like i said there are some samples I have seen here but they seem to only cover calculating the no. of workdays between 2 dates. I've also tried the Dateadd function but it didn't work.

Again, I'm a newbie so I'm not that all techie with the VBA language. I'm trying but you just have to be patient with me.

Thanks in advance.

 

[Uncle Gizmo]

For example there's an ongoing thread in this very forum (here) about it.
Cheers.

Working Link

 

[vspsdca]

Thanks Tony...

Just looking through it already confuses me. I'll read all the articles you have suggested and will shoot you guys a message if i have any questions...

Thanks to you too Leigh for taking the time to respond to my post.

Best regards.

 

[raskew]

Hi -

This function will give you a start, allowing you to add or subtract workdays from a specified date.

It's limited in that it considers Monday - Friday to be workdays, with no option to select other days. And, there's no provision for holidays.

I'm working on an update, which I'll post when complete.

Function UpBusDays3(pstart As Date, _
                    pNum As Integer, _
                    Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose:   Add or subtract business days
'           from a date
'Coded by:  raskew
'Inputs:    1) +) ? UpBusDays3(#2/17/06#, 3, True)
'           2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output:    1) +) 2/22/06
'           2) -) 2/17/06
'*******************************************
                    
Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer

    dteHold = pstart
    n = pNum
    For i = 1 To n
       If pAdd Then  'add days
          dteHold = dteHold + IIf(WeekDay(dteHold) > 5, 9 - WeekDay(dteHold), 1)
       Else          'subtract days
          dteHold = dteHold - IIf(WeekDay(dteHold) < 3, Choose(WeekDay(dteHold), 2, 3), 1)
       End If
    Next i
    UpBusDays3 = dteHold
    
End Function

HTH - Bob

 

[gemma-the-husky]

logically, to evaluate working days is tricky

it depends on
a) which days are non-working days
b) which day you start calcluating from

so the calculation is much harder than just evaluating elapsed time. At its simplest, you need a mechanism to determine how many full weeks have passed, and whether the elapsed period spans an additional weekend. You also need to establish how mmany holiday days have elapsed - which will vary between country.

There are still a number of examples here, but this should clarify why the code is quite complex.

 

[LPurvis]

Sorry about that link. Trying to shorten the hyperlink displayed text obviously went wrong on that one. Thanks for the fix Tony.

However my second link includes two functions.
Net and Add work days. The second of which will surely give you what you want?

Inevitably with these functions you need to have entered holidays into a holiday table against which the code compares.

(There is no IsHoliday VBA or SQL function lol
Wouldn't that be handy lol
IsHoliday("England", Date())
of course MS would have to supply an online reosurce from which to retrieve it - a Webservice most likely. In the mythical Office.NET perhaps when we all have fibre optic connections. :-s)

Cheers.