## The Opportunity

How many days in a month? showed following table, created using the EOMONTH() function, plus NOW() and a bit of formatting.

This is fine, but most organizations think in terms of weekdays. Therefore, the following was created by hiding 3 columns (D, E, and F) then and adding four.

## What's New

**First Week Day**calculates the first day of the month then adjusts "forward", adding days if the first of the month is a Saturday or Sunday.

- First Day of the Month. As before, this finds \the last day of the previous month, then adds a day

EOMONTH(NOW(),A2-1)+1 - Adjust for Saturday or Sunday, by adding 1 day for Sunday or 2 for Saturday

+IF(WEEKDAY(EOMONTH(NOW(),A2-1)+1,11)<6

,0

,8-WEEKDAY(EOMONTH(NOW(),A2-1)+1,11)) - Test if this is Saturday of Sunday
*WEEKDAY(EOMONTH(NOW(),A2-1)+1,11)<6* - 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 Week Day**takes the EOMONTH() date then, if the last day of the month adjusts to the previous Friday.

=B2-MAX(0,WEEKDAY($B2,11)-5)

- Last Day of the Month, as before, is calculated in column B.

=B2 - Adjust for Saturday or Sunday, this time by subtracting 1 for Saturday, 2 for Sunday

-MAX(0,WEEKDAY($B2,11))-5) - 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,

**Days from 1st to Last Weekday**calculates how many days are in this adjusted work month simply by using the DAYS() function, then adding one to make it inclusive of the first day.

**Weekdays in Month.**Could have built a table of dates for the months then used a complex COUNTIF() statement/ Instead I wrote the following VBA function.

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

End If

Next i

' Workdays = EndDate - StartDate + 1

End Function