CODEWITHSUNDEEP
ms-accessvba
RossTurner
RossTurner

Reputation: 11

Add Working Days to Start Date to Create End Date (including Bank Holiday)

I've modified this access function so I can work out the end date given the amount of working days.

However I need to be able to take uk bank holidays into consideration as well.

Anybody got any good idea how I could do that ?

Public Function CountDays(startDate As Date, NoOfDays As Integer) As Date
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim tmpStartDate As Date
Dim i As Integer

tmpNo = NoOfDays

tmpStartDate = startDate

tmpDate = startDate

i = 0

Do Until i = NoOfDays

If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
tmpNo = tmpNo + 1
Else
i = i + 1
End If
tmpDate = tmpDate + 1
Loop

CountDays = DateAdd("d", tmpNo, tmpStartDate)
End Function

cheers

Ross

Upvotes: 1

Views: 4688

Answers (2)

Tom Collins
Tom Collins

Reputation: 4069

No need to loop through every day. A bit of calculation will get you the number of weekdays, then do a table lookup for the number of holidays in that time frame. So:

TotalDays = DateDiff("d", startDate, endDate) + 1

TotalWeekends = (DateDiff("ww", startDate, endDate) * 2) + _ 
((DatePart("w", startDate) = vbSunday) *-1) + _ 
((DatePart("w", endDate) = vbSaturday) *-1)

TotalHolidays = DCount("*","tblHolidays","Holiday Between #" & startdate & "# And #" & enddate & "#")

TotalWorkingDays = TotalDays - TotalWeekends - TotalHolidays

By the way, if you use the # around the dates, Access assumes it's in the US format. You can use quotes instead.

Upvotes: 2

RichardC
RichardC

Reputation: 794

As UK bank holidays differ each year, you would need to create a table to store these dates in, and then check this table in your code and increase your tmpNo value if the date exists.

Change the code by adding in an extra 'Else If' clause as follows:

Do Until i = NoOfDays
    If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
        tmpNo = tmpNo + 1
    Else If DCount("*","tblExceptionDates","dtmDate = #" & tmpDate & "#") > 0 Then
        tmpNo = tmpNo + 1
    Else
        i = i + 1
    End If
    tmpDate = tmpDate + 1
Loop

Upvotes: 0

Related Questions