I recently needed to calculate the period number of a current date for a client. The current period is not exactly the current week number, it’s a specific week, but it could start on any particular day and end on any particular day.

In my case, they specify it for a year. My calculations are lacking slightly in that I’m not checking to see if I’m going outside the bounds of the start date (or actually the 1st 7 day period end date) and the last 7 day period date. That information is stored in a table for each year.

Here is the top level of the function I’m using for this procedure:

Public Function GetPeriodForGivenDate(theDate As Date) As Long
    Dim FirstPeriodEnds As Date
    FirstPeriodEnds = NiceDLookup("time_card_firstperiodends", "time_card_years", "time_card_current_year=True")
    Dim WeekDayPeriodEnds As Integer
    WeekDayPeriodEnds = DatePart("w", FirstPeriodEnds)
    Dim DateToSameWeekday As Date
    DateToSameWeekday = DateAdd("d", WeekDayPeriodEnds - DatePart("w", theDate), theDate)
    Dim WeekDifference As Long
    WeekDifference = DateDiff("w", FirstPeriodEnds, DateToSameWeekday)
    GetPeriodForGivenDate = WeekDifference + 1
End Function

So my basic procedure here is that:

1. I am looking up the date the first period ends. In this case that date is 12/30/2023
2. Next I am figuring out what weekday that is. This is so I can convert today’s date to the end of the “week” of the period. This will make calculations simple. In this case, 12/30/2023 is a Saturday, which calculates to a 7. (this can be different depending on how you specify options. Like if you are considering Monday the start of the week instead of Sunday, it would actually be the number 6)
3. Now I want to convert the date provided to the function to the next Saturday. I do this using a formula to add the number of days to the date where the number of days are the day of the week I want minus the day of the week the date actually is. This may actually have some problems for days other than Saturday which I haven’t really tested yet, but it works for Saturdays where Saturdays are the 7th day.
4. Next I use the DateDiff function to find the number of weeks between the date the first period ends and the calculated period end date based on the current date.
5. Since the final number if the periods are the same would be zero, I want to add one to the result. And voila! I get the current period number to return.