Number of Work Days
Use NETWORKDAYS to count the number of work days between two dates
Create a custom function in VBA to handle odd situations
|
Q: |
I need to calculate the number of work days between
two dates. I understand the NETWORKDAYS function allows me to
calculate Monday thru Friday, but we also work on Saturdays. How can
I count the number of days between two dates, but exclude all of the
Sundays only?
|
|
|
|
|
A: |
Very good question. Yes, you can use NETWORKDAYS if
you only need to worry about excluding Saturdays, Sundays, and a
list of holidays. However, if you have a custom requirement like
yours, you'll need to write your own function using VBA. Here is the
function I wrote.
Note that in the video, the end of the first line of code says "as
Date" but it should be "as Long". It works fine in both cases, but
you should return a Long instead of a Date value.
|
|
|
Public
Function MyWorkDays(D1 As Date, D2 As Date)
As Long
DayCount = 0
For X = D1 To D2
D = Weekday(X) '1=sun, 7=sat
If D <> 1 Then DayCount = DayCount + 1
Next X
MyWorkDays = DayCount
End Function
|
|
|
|
|
|
Here is a FREE video
tutorial that shows you how to set up this function step-by-step:
|
|
