# Calculate Weeks in A &quot;Fiscal Month&quot;

Our companies fiscal year starts on the last Sunday in July. Each month starts on the last Sunday of the previous month. I will be given a date and I will have to give the number of weeks in that "Fiscal Month". For example

Su      M      T      W      Th      F      S
August
31      1      2      3      4      5      6
7      8      9      10      11      12      13
14      15      16      17      18      19      20
21      22      23      24      25      26      27
September
28      29      30      31      1      2      3
4      5      6      7      8      9      10
11      12      13      14      15      16      17
18      19      20      21      22      23      24
October
25      26      27      28      29      30      1
2      3      4      5      6      7      8
9      10      11      12      13      14      15
16      17      18      19      20      21      22
23      24      25      26      27      28      29

August and September only has 4 weeks. October has 5 weeks.
I would prefer not to build a table for each fiscal year show the number of weeks for the month for the date given. I had thought of select case statements but I haven't been able to get my arms around this yet. Thanks for any help.

Graham
Microsoft AccessVBA

Last Comment
Gustav Brock

8/22/2022 - Mon
Anders Ebro (Microsoft MVP)

While working with stuff such as fiscal years, I strongly urge you to use a date table, that includes your companys fiscal year preference.
If you follow the example here:
https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

You can then query stuff like weeks in a quarter by:
``````SELECT year,Quarter,COUNT (DISTINCT ISOWeekOfYear) FROM dbo.DateDimension
GROUP BY Year,Quarter
``````
Note, that the example here doesn't include fiscal quarters, but could be modified to do so.
Gustav Brock

You can find the last Sunday of any given month with this function:

``````' 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.

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.
End If
End If

DateWeekdayInMonth = ResultDate

End Function
``````
Call it like this to find the start date for a financial month of yours:

``````    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

``````
To find week number, you can use:

``````CurrentFiscalWeek = DateDiff("w", DateFiscalStart, 5, vbSunday), Date) + 1
``````
/gustav
Graham Gibson

Thanks. I will be reviewing this today.
Graham Gibson

Gustav,
I created a database to show the key fiscal dates. The query qryforfiscalcheck shows the type of data I am working with. MyDate is the only manual entry I really want to see. I would like to the database to figure the FiscalMonth, FiscalYear and WeeksInFiscal.  A couple of key dates is for example 7/30/2016 is in the FiscalYear 2016 not 2017. 7/31/2016 is in the Fiscal Month 8 in the fiscal year 2017. The fiscal year runs from the Last Sunday in July to the last Saturday in July the following year. July 31, 2017 until July 29, 2018 for this year is FiscalYear 2017. Fiscal Months run from the last Sunday of the previous month to the last Saturday of the current month. One critical calculation I need the database to make is the WeeksInFiscal month. FOr example this year August has 4 weeks. September has 4 and October has 5 for the fiscal month. I hope this helps. Thanks for any help.
Graham
Graham Gibson

Here is the file.
MyTime.accdb
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Graham Gibson