# Calculate Number of Business Days Between Two Dates

1. ## Calculate Number of Business Days Between Two Dates

Anyone have a simple VBA solution to calculate the number of business days between two dates?

2. From the similar thread section below:

3. The following is a simplified version (9 lines of code) I was able to figure out for calculating the total number of business days between two dates.

Code:
```Dim BusinessDays As Integer
Dim TotalDays As Integer
Dim Sunday As Integer
Dim Saturday As Integer
TotalDays = DateDiff("d", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
Sunday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 1)
Saturday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 7)
BusinessDays = TotalDays - Sunday - Saturday
The following is an image of my calculator followed by the corresponding code:

Code:
```Private Sub btnCalculate_Click()
On Error GoTo btnCalculate_Click_Err
'Calculate Number of Days, Hours, and Minutes
Me![txtDateDaysCalculated] = DateDiff("d", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
Me![txtDateHoursCalculated] = DateDiff("h", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
Me![txtDateWeeksCalculated] = [txtDateDaysCalculated] / 7
Me![txtTimeHoursCalculated] = Int(DateDiff("n", [txtBeginTimeRangeFilterCalculator], [txtEndTimeRangeFilterCalculator]) / 60)
Me![txtTimeMinutesCalculated] = Format(DateDiff("n", [txtBeginTimeRangeFilterCalculator], [txtEndTimeRangeFilterCalculator]) Mod 60, "00")
Me![txtHourFraction] = [txtMinutesToCalculate] / 60
Dim TotalDays As Integer
Dim Sunday As Integer
Dim Saturday As Integer
TotalDays = DateDiff("d", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
Sunday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 1)
Saturday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 7)
BusinessDays = TotalDays - Sunday - Saturday
btnCalculate_Click_Exit:
Exit Sub
btnCalculate_Click_Err:
Beep
MsgBox Err.Description, vbOKOnly, ""
Resume btnCalculate_Click_Exit
End Sub```

4. That's fine if you don't need holidays excluded. Many situations do.

5. The issue with tracking holidays is not everyone in the business world has holidays off. Moreover, we cannot feasibly track the number of "business days" before or after the holiday that people take off either. So I am not concerned with tracking holidays. It is easier for the person who is using the calculator to subtract the number of business days the person is not going to be in the office for a given holiday than it is to program that.

The other problem is, for example, Martin Luther King Day. The holiday is/was not always observed on January 15. I remember different entities years ago observing it on January 17.

6. I think in normal usage it isn't intended to account for anybody's holidays other than the company running the app. That's why there's normally a holidays table (as in the code referenced above). I just wanted to point out that your method didn't account for them in case someone searching for a solution later comes upon this thread and their situation does need to account for them.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums