Avatar of Graham Gibson
Graham Gibson
 asked on

Calculate Weeks in A "Fiscal Month"

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

Microsoft AccessVBA

Avatar of undefined
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:

You can then query stuff like weeks in a quarter by:
SELECT year,Quarter,COUNT (DISTINCT ISOWeekOfYear) FROM dbo.DateDimension
GROUP BY Year,Quarter

Open in new window

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, _
        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

Open in new window

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

Open in new window

To find week number, you can use:

CurrentFiscalWeek = DateDiff("w", DateFiscalStart, 5, vbSunday), Date) + 1

Open in new window

Graham Gibson

Thanks. I will be reviewing this today.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Graham Gibson

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 Gibson

Here is the file.
Gustav Brock

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Graham Gibson

Gustav, That is exactly what I wanted. I knew it was possible. Thanks for the solution.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

You are welcome!