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

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

week N.yxzp

Date Time

Accepted answers

All comments

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!

Solution_Nouveau workflow2.yxmd

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. 🙂

Solution_Nouveau workflow2.yxzp

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703