Week number formulas

Share your Space
1

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 :thinking:

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) :crazy_face:
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.

8 Likes
2

Hey @Chr1sG,

These formulas are great for getting the week year/week number from a date, but is there a equivalent you could help create that would essentially do the opposite, which is to get a specific date from a week number/week year/day of the week?

For example, if I had the week year (2024) and week number (2) defined and wanted to find the actual date of the Monday of that week, the result would be something like 01-08-2024 (MM-DD-YYYY)?

3 
Date(Year, 1, 1) -
  Days(
    If(WeekDayName(Date(Year, 1, 1)) = "Monday", 7,
    If(WeekDayName(Date(Year, 1, 1)) = "Tuesday", 8,
    If(WeekDayName(Date(Year, 1, 1)) = "Wednesday", 9,
    If(WeekDayName(Date(Year, 1, 1)) = "Thursday", 10,
    If(WeekDayName(Date(Year, 1, 1)) = "Friday", 4,
    If(WeekDayName(Date(Year, 1, 1)) = "Saturday", 5, 6))))))
  ) +
  Days(Week * 7)

This gets you the Monday for a given Year and Week

(it has no error checking, so invalid year or week numbers will give odd results)

If you need a different day of the week, you could add something like this

+ Days(DayOfTheWeek -1)

where DayOfTheWeek is a number between 1 and 7 to represent Monday to Sunday.

1 Like
4

Thanks for sharing, I can see how that will be useful for me in the future.

Also interesting to hear how week numbers work.

5

Thank you!