Building An Array of All Fridays In A Month (Could Be Any Weekday)
Some business reports need data segregated into 7-day groups ending on a particular day (Friday in this example). The same day of the month (i.e. Friday) can occur a maximum of 5 times in a given month (i.e. there are 5 Fridays in some months). The following routine will build an array of all the Friday dates in a given month.
Program Code
Option Explicit
Option Base 1
Public Sub Fridays()
' *********************************************************************
' Compute All The Fridays In The Sales Rep Reporting Month
' *********************************************************************
Dim dteReportMonth As Date
Dim dteFridaysInMonth(5) As Date
Dim dteReportDateStart As Date
Dim dteReportDateEnd As Date
Dim intFridayCount As Integer
Dim dteDateCycle As Date
' *********************************************************************
' Choose The Current Date For The Month and Compute The First and Last
' Day of the Month
' *********************************************************************
dteReportDateStart = DateSerial(Year(Date),Month(Date),1)
dteReportDateEnd = DateSerial(Year(Date),Month(Date)+1,0)
intFridayCount = 0
' *********************************************************************
' Cycle Through All Days of the Month
' *********************************************************************
For dteDateCycle = dteReportDateStart To dteReportDateEnd Step 1
If Weekday(dteDateCycle) = vbFriday Then
intFridayCount = intFridayCount + 1
dteFridaysInMonth(intFridayCount) = dteDateCycle
End If
Next dteDateCycle
End Sub
