The week number is a function that represents the week’s count starting on the 1st of January. So the week that contains the first day of the week is the first week. The WeekNum function also enables you to define the first day of the week since it varies from country to country.

So let’s see how we can use it efficiently.

Usage

It follows a simple pattern where you provide a date and the start of the week. Then, it returns an integer com the week number. By default, the week starts on Sunday.

Example:

WeekNum(Today())

today is the 16th of August 2021, so it will return:

34

The second parameter is optional, and you need to pass a number that represents the start of the week. For example:

WeekNum(Today(),1)

today is the 16th of August 2021, so it will return:

34

The above examples are the same since 1 represents Sunday. The remaining numbers are (warning, they are strange, so see the table below with the codes). Let’s look at something a bit better by using the StartOfWeek enumeration.

Looking at the same example as above:

WeekNum(Today(),StartOfWeek.Sunday)

today is the 16th of August 2021, so it will return:

34

Heres’s the table with all the combinations.

Excel Code StartOfWeek End Day of the Week Start Number Description
1 or 17 StartOfWeek.Sunday Saturday 1 Default value
2 or 11 StartOfWeek.Monday Sunday 1 You can use the ISOWeekNum function to achieve the same
3 StartOfWeek.MondayZero Sunday 0 Starts at zero instead of one
12 StartOfWeek.Tuesday Monday 1  
13 StartOfWeek.Wednesday Tuesday 1  
14 StartOfWeek.Thursday Wednesday 1  
15 StartOfWeek.Friday Thursday 1  
16 StartOfWeek.Saturday Friday 1  

Pay especially at the “StartOfWeek.MondayZero”. It’s the one that is slightly different than the others. If you need, for some reason, to start the count at zero, you can use this option, but Monday is the only day of the week that you can do this.

Limitations

You need to provide a date to get the week number. Even if you provide a string with the correct date, you need to convert it to a valid date to get the week number.

Also, this won’t convert dates before the reference date, being the 1st of January 1900.

Recommendations:

If you want the week to start at a Monday

You can also use the ISOWeekNum function to get the same result with only the date parameter.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested WeekNum functions in a formula, you should review it and make everything more straightforward.

Localization

Please note that formulas may have localization-based differences. For example, you should write “WeekNum” with "," separating each of the arguments, but if your regional settings are set to Portugal, you should use ";" instead.

Sources:

 ISOWeekNum functions in Power Apps .

Back to the Power Apps Function Reference

Photo by Rohan on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: