Issue with Business Dates and Holiday calculation

[mikeh1992]

Hi All,

I am using the below code to calculate number of business days excluding Holidays. The code works great unless the date lands on a holiday. For some reason the current code I have does not register the "Do Until" statement correctly when I = holiday date (works fine if the holiday is not the final day. Do you know what might be wrong with my "Do Until" Statement?

This is what I am using for my do until. I put in variables to test if the conditions were being met and it did seem that they were. I'm thinking my error lies with the "Dcount" statement?

Do Until i = NoOfDays And Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And DCount("*", "tblExceptionDates", "[Holiday] = #" & tmpDate & "#") > 0

Below is the full code:

Public Function CountDays1(startDate As Date, NoOfDays As Integer) As Date
' Function to count no of working days
Dim tmpDate As Date
Dim tmpStartDate As Date
Dim i As Integer
Dim dbs As Object
Dim rst_holiday_table As Recordset
tmpNo = NoOfDays
tmpStartDate = startDate
tmpDate = startDate
Set dbs = CurrentDb
Set rst_holiday_table = dbs.OpenRecordset("SELECT tblExceptionDates.Holiday FROM tblExceptionDates;")
i = 0

Do Until i = NoOfDays And Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And DCount("*", "tblExceptionDates", "[Holiday] = #" & tmpDate & "#") > 0

If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
tmpNo = tmpNo + 1
ElseIf DCount("*", "tblExceptionDates", "[Holiday] = #" & tmpDate & "#") > 0 Then
tmpNo = tmpNo + 1
Else
i = i + 1
End If
tmpDate = tmpDate + 1
Loop
End If
CountDays1 = DateAdd("d", tmpNo, tmpStartDate)
End Function

Thanks and Best Regards,

Mike

 

[RuralGuy]

I'm a bit confused by your code. I would think that:
Public Function CountDays1(startDate As Date, NoOfDays As Integer) As Date
would be more like:
Public Function CountDays1(StartDate As Date, LastDate As Date) As Long
Which would then count the number of "working" days between the two dates.

 

[mikeh1992]

That part works fine. I'm calculating the number of business days between a pick up date an a specific allowed number of days for something to be in transit for. So this is correct.

The issue lies within being able to have the do until statement register when landing on a holiday. According to the variable I put in to test, the code is recognizing the holiday, however it is passing the do until, allowing the wrong date to be populated.

Do Until i = NoOfDays And Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And DCount("*", "tblExceptionDates", "[Holiday] = #" & tmpDate & "#") > 0

 

[Pat Hartman]

I've attached a database that should help you. You'll have to update the Holiday dates table with the days that are holidays for your company.

 

[mikeh1992]

Thank you!! I'm a little optimistic about opening a DB. Is there code similar to what I'm looking to do that I could paste into SQL and play around with?

 

[RuralGuy]

You can trust *anything* that Pat posts. She is an excellent helper.

 

[Pat Hartman]

Thanks for the vote of confidence Allan.

Mike, the database contains a bunch of useful date functions including two that you need. One finds the difference in business days between two dates and the other adds a number of days to a date and returns the next business date that. So if date + days falls on a weekend or holiday, the date is incremented until it falls on a business day. Remember, you'll need to populate the holiday table. The data in it is old and not relevant to your company anyway since companies observe different holidays.