Thursday, November 16, 2017

Weekdays in a month.

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
  • 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 Week Day takes the EOMONTH() date then, if the last day of the month adjusts to the previous Friday.
  • 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,
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

No comments:

Post a Comment