Week number formulas

After a couple of people asked about calculating week numbers for a given date, I thought I’d share these two formulas that you might find useful:

WeekYear:

``````Year(DateField + Days(
If(WeekDayName(DateField) = "Monday",3,
If(WeekDayName(DateField) = "Tuesday",2,
If(WeekDayName(DateField) = "Wednesday",1,
If(WeekDayName(DateField) = "Thursday",0,
If(WeekDayName(DateField) = "Friday",-1,
If(WeekDayName(DateField) = "Saturday",-2,
-3))))))))
``````

The above formula tells you which year a given date is in

This might sound crazy, but 1st Jan 2023 is actually in the year 2022 as far as week numbers are concerned (it’s week 52) and 31st Dec 2024 is actually in the year 2025 (week 1)
It is the Thursday in any given week that determines which year that week falls under.

Then, this formula can be used as follows to work out the week number:

``````RoundUp(ToDays(DateField -
(Date(WeekYear,1,1) + Days(
If(WeekDayName(Date(WeekYear,1,4)) = "Monday",2,
If(WeekDayName(Date(WeekYear,1,4)) = "Tuesday",1,
If(WeekDayName(Date(WeekYear,1,4)) = "Wednesday",0,
If(WeekDayName(Date(WeekYear,1,4)) = "Thursday",-1,
If(WeekDayName(Date(WeekYear,1,4)) = "Friday",-2,
If(WeekDayName(Date(WeekYear,1,4)) = "Saturday",-3,
-4))))))))) / 7,0)
``````

This works by determining the last day of the previous year (using the rule that the 4th January is always in week 1) and calculating how many weeks (incl fractions of a week) have elapsed since then.

Hope this is useful.

5 Likes