Week number from date - starting from 1

[Krzysztof_Hyla]

Hi.

What is the formula to extract week number from date?

I used this formula so far:

DateTimeFormat([Date], '%W')

But it starts to count from 0 if the first day of the year is not Monday.

How to make it count from 1?

[ChrisTX]

Can you convert the Week to numeric, then add 1?

[Krzysztof_Hyla]

I can, but this will result in having first week of year 2018 and 2024 as number 2 (because 1st Jan is Monday)

I can hardcode an exception for these years, but I'm rather looking for a consistent formula if possible

[ChrisTX]

See if the attached workflow gives the results you want

Chris

[ChrisTX · Accepted solution]

For additional common date dimensions

see this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-drop-down-list-boxs-to-populate-...

and look at the file Date_Solution.yxzp, which includes the macro Dim_Datetime_Custom.yxmc

Chris