Finding week number / It gives difference results between excel and alteryx

[Ayari]

hello everyone , i m attaching a work flow that gives the week number of  a date but the probleme is when we use this in excel 

"=SI(DROITE(TEXTE(O139;"JJ/MM/AAAA");4)="2020";NO.SEMAINE(O139);NO.SEMAINE(O139)-1)" it gives you the week number but if alteryx it gives others results , i will give you exemple :

02/12/2020 it guves week number 48 but with excel it gives us 49

and if we add a year to that  , that's mean 

02/12/2021 it gives you also 48 with alteryx

[Sebastiaandb]

Hi @Ayari  

It has to do with the way Alteryx and Excel calculate weeks, check out:

https://stackoverflow.com/questions/38377054/excel-weeknum-function-returns-wrong-week 

https://help.alteryx.com/20213/designer/datetime-functions 

for the last link, look up the specifier. Alteryx starts counting at "0", so the first week is "0". Excel starts counting at "1". 

Also, it can differ depending if you use specifier %U or %W in Alteryx (start on Sunday or Monday), for Excel you can also alter that:

https://support.microsoft.com/en-us/office/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340 

Hopefully this helps! 🙂 Good luck!

[Ayari]

thnx for your answer but the result is false !!

the week number should not be the some .

for the 2020-12-02 it gives you 49 but for the date 

           2021612-03 it gives you also 49 

i m loosing it ...

[Sebastiaandb]

@Ayari 

Haha no worries, 

I added some dates for you to clarify. You can see that when you use specifier "U" it starts counting the sunday as the new week. If you  use "W" it will start from the Monday. 🙂

[Ayari]

take this exemple please and see what goes wrong 

if you add a year nothing changes !!!

[Ayari]

Look to the last exmples you can see that if you add a year nothing chnages but if you look at it in google you will se that the week number of the date 01/12/2021 is 48 and not 49 !!: you see what i talking about?

[Ayari]

if we add a year nothing changes why? in excel it does changes and gives the correct result

[Ayari]

@Sebastiaandb look what i found , and thnk you

[shreyanshrathod]

I think, Datetimeformat(Date,"%U") should give you correct results.

Regards,

Shreyansh