ISO Week number returning incorrect week number

Hey All,

I'm using the formula mentioned below to get a ISO week number but for date:1/2/2021 its turning week 53 instead of week 1, any ideas how to fix it? Thanks.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-date-to-week-number-of-that-year/td-p/158309

PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([Field1],4-Switch(DateTimeFormat([Field1],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([Field1],4-Switch(DateTimeFormat([Field1],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/7)+1),2,"0")

Date Time

Help

Accepted answers

kelsey_kincaid

Hi @BosKev ,

Based on ISO, it looks like week 53 does include 2021-01-02 based on the below hyperlink:

https://www.epochconverter.com/weeks/2021

Is there a different system you're wanting to use to reflect week number?

All comments

kelsey_kincaid

Hi @BosKev ,

Based on ISO, it looks like week 53 does include 2021-01-02 based on the below hyperlink:

https://www.epochconverter.com/weeks/2021

Is there a different system you're wanting to use to reflect week number?

JagdeeshN

Hi @BosKev ,

Please find below a sample workflow to extract the week number.

I think you are looking at a date in the format MM-dd-yyyy thus making 1/2/2021 week 1.

Do let me know if this helps.

Best,

@JagdeeshN

Week Number.yxmd

BosKev

interesting, so the year 2021 counts backwards from week 53 to 1?

BosKev

Nvm, one of my co-worker explained to me something bout the day count in 2020 that first few days is actually wk 53 for 2020. I just have to figure out a way to display to the end user. Thanks.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335