[DONE] Week number formulas

Fibery Showcase
1

UPDATE: improved formulas below.
UPDATE 2: native week number function is now available.

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.

10 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!

6

The formulas can now be simplified as follows:

WeekYear:
Year(DateField + Days(4 - Weekday(DateField)))

WeekNum:

RoundUp(
  ToDays(
    DateField - (Date(WeekYear, 1, 1) + Days(3 - Weekday(Date(WeekYear, 1, 4))))
  ) / 7,
  0
)
1 Like
7

A bunch of things broke for us over the new years holiday because the formula was spitting out that Dec 30 and Dec 31 were in week 53, instead of week 1!

@Chr1sG, is there a more reliable week number formula you could help craft that spits out a value that is compliant with the ISO-8601?

They define the first week of the year as the one that contains the first Thursday, or in other words, the first week that has at least 4 days in the year.

and is there a dedicated “WeekNum” function on the horizon that will negate the need for these complex formulas all together?

8

Actually, I see the error in our ways. We were using the week num formula you have, but just setting the week year as the exact calendar year instead of formatting it like yours.

So it all works as expected, was just hoping to not use 2 formulas. Guess we would create one mega formula if we wanted, right?

RoundUp(
  ToDays(
    DateField - (Date(Year(DateField + Days(4 - Weekday(DateField))), 1, 1) + Days(3 - Weekday(Date(Year(DateField + Days(4 - Weekday(DateField))), 1, 4))))
  ) / 7,
  0
)
9

Should work fine, I assume.

10

Yup. Possibly v soon.

1 Like
11

Formula IsoWeekNum([Date]) → 23 released today