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:

- I am looking up the date the first period ends. In this case that date is 12/30/2023
- 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)
- 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.
- 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.
- 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.

This function would be a perfect candidate for my Python-inspired Doc Tests feature: https://nolongerset.com/python-inspired-doc-tests-in-vba/.

That feature looks pretty nifty. Thanks. Looking back on this function, I should probably have written it using Test Driven Development and could have written some RubberDuckVBA tests for the functionality it is meant to deliver. In fact, I think I’ll go do that now.

Since I’m already using RubberDuckVBA’s testing framework, it’s simple for me to implement.

I like the idea of having the tests documented right next to the function in the comments though.

> I like the idea of having the tests documented right next to the function in the comments though.

Yeah, doc tests are ideal for pure functions (https://nolongerset.com/what-are-pure-functions/) like this one.

Methods with side effects–or those encapsulated in classes–benefit from a more full-featured testing framework with support for setup and teardown, additional assertions beyond equality, etc.