Call it like this to find the start date for a financial month of yours:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth. ' ' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed. ' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed. ' ' If Weekday is invalid or not specified, the weekday of DateInMonth is used. ' ' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH. ' Public Function DateWeekdayInMonth( _ ByVal DateInMonth As Date, _ Optional ByVal Occurrence As Integer, _ Optional ByVal Weekday As VbDayOfWeek = -1) _ As Date Const DaysInWeek As Integer = 7 Dim Offset As Integer Dim Month As Integer Dim Year As Integer Dim ResultDate As Date ' Validate Weekday. Select Case Weekday Case _ vbMonday, _ vbTuesday, _ vbWednesday, _ vbThursday, _ vbFriday, _ vbSaturday, _ vbSunday Case Else ' Zero, none or invalid value for VbDayOfWeek. Weekday = VBA.Weekday(DateInMonth) End Select ' Validate Occurence. If Occurrence <= 0 Then Occurrence = 1 ElseIf Occurrence > 5 Then Occurrence = 5 End If ' Start date. Month = VBA.Month(DateInMonth) Year = VBA.Year(DateInMonth) ResultDate = DateSerial(Year, Month, 1) ' Find offset of Weekday from first day of month. Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek ' Calculate result date. ResultDate = DateAdd("d", Offset, ResultDate) If Occurrence = 5 Then ' The latest occurrency of Weekday is requested. ' Check if there really is a fifth occurrence of Weekday in this month. If VBA.Month(ResultDate) <> Month Then ' There are only four occurrencies of Weekday in this month. ' Return the fourth as the latest. ResultDate = DateAdd("d", -DaysInWeek, ResultDate) End If End If DateWeekdayInMonth = ResultDate End Function
To find week number, you can use:
Dim Year As Integer Dim Month As Integer Year = 2016 Month = 10 ' Calendar month. DateMonthStart = DateWeekdayInMonth(DateSerial(Year, Month - 1, 1), 5, vbSunday) ' Result -> 2016-09-25 DateFiscalStart = DateWeekdayInMonth(DateSerial(Year, 7, 1), 5, vbSunday) ' Result -> 2016-07-31
CurrentFiscalWeek = DateDiff("w", DateFiscalStart, 5, vbSunday), Date) + 1
If you follow the example here:
You can then query stuff like weeks in a quarter by:
Open in new windowNote, that the example here doesn't include fiscal quarters, but could be modified to do so.