How many days in a month? showed following table, created using the EOMONTH() function, plus NOW() and a bit of formatting.
- First Day of the Month. As before, this finds \the last day of the previous month, then adds a day
- Adjust for Saturday or Sunday, by adding 1 day for Sunday or 2 for Saturday
- Test if this is Saturday of Sunday
- EOMONTH() returns the last day of the Month
- Weekday(date,11) returns 6 for Saturday, 7 for Sunday
(Note: WEEKDAY(<date>,2) would do the same)
- Adds 0 if this isn't Saturday or Sunday
- Calculates how much to adjust if Saturday or Sunday
- WEEKDAY(<date>,11) ) returns 1 for Monday through 7 for Sunday.
- Subtracts WEEKDAY() result from 8, making the forward adjustment one day for Sunday (7), two for Saturday (6).
- Last Day of the Month, as before, is calculated in column B.
- Adjust for Saturday or Sunday, this time by subtracting 1 for Saturday, 2 for Sunday
- As before, if this is not a weekend day, make no adjustment by returning 0
because subtracting 5 from 1 (Monday) through Thursday (4) returns a negative, meaning the maximum is 0.
- If it is a weekend day, the WEEKDAY() function returns 6 (Saturday) or 7 (Sunday).
- Subtracting 5 from WEEKDAY() for Saturday result in moving the date back 1 day, Sunday for 2 days,
Public Function Workdays(StartDate, EndDate) As Integer
' Returns how many workdays from a starting date to an ending date
Dim i As Integer
Dim FirstDate As Date
Dim LastDate As Date
For i = 1 To LastDate - FirstDate
If Weekday(FirstDate + i, 12) < 6 Then
Workdays = Workdays + 1
' Workdays = EndDate - StartDate + 1