Week Number 1 different between excel and Alteryx

[The_Fray]

Hello,

When us the Excel Function WeekNum and the Alteryx Function DateTimeFormat to determine what week of the year a date it, why is it that I have to add an extra week to the Alteryx version to match with the Excel? It is easy enough to add an extra  days, but id also like to understand why that is the case to make sure an error very arises with my workaround (add 7 days).

Thanks,

[Loic · Accepted solution]

Look at this post. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-the-week-number/m-p/54371/...

It is because Alteryx and Excel don't use the same standard or rules to calculate the week.

The post contains ways to fix it - in some cases it is more complicated than adding 1. Rules to count weeks are different depending on the country you are doing business. Some countries consider that the first week of a year need to be full, some others partial...same for the end of the year...

[The_Fray]

Thanks, appreciate it. 

To be consistent, I am going to use the ISO standard. Looks like there are a few Alteryx formulas people have created to convert from Alteryx standard to ISO.