Convert date to week number of that year

[Longdaica]

Hi all

i used this formula to convert date to week. But something is wrong 

ToNumber(DateTimeFormat([DATE],'%U'))+1      <= I have taken from this forum

I found out that when i try to convert 29/12/2017 , it gave me week 53 of 2017

but when i tried different tool on the internet, it gave me week 52..... I am pretty sure that 2017 is not a leap year !!!!

[BenMoss]

ToNumber(DateTimeFormat([DATE],'%U'))+1 

I don't see why you need the plus 1.

Lets take 2017-01-01, applying this formula returns 1 ToNumber(DateTimeFormat([DATE],'%U'))

Which is right, this is the first week. With the +1 at the end it makes it week 2.

I would simply trim off the +1, not sure why that exist.

Ben

[Longdaica]

Hi Benmoss

If I don't put the +1 , 1/1/2018 will be week 0 

Is there any other way to convert week from date correctly ?

[BenMoss]

Here's how you could go about doing it.

Create a field which represents the first day of the year for the given value. This can be acheived with the datetimetrim([field],'year') function.

Then create a calculation that calculates the difference, in days, between your date field and this trimmed date field and add 1.

datetimediff([Field1],[FirstDateYear],'days')+1

Now you can divide this value by 7.

Finally wrap this in ceil() to return the rounded up value.

Example attached.

Ben

[Longdaica]

Hi Ben

The way you did is understandable but   it does not work with LEAP years (2016,2020)  because you always consider 1/1 is the first day of the first week of a year .

[BenMoss · Accepted solution]

There is no single answer to your question, the notion of week numbers depends on the system you are trying to apply.

I've found this one which was found in this thread: https://community.alteryx.com/t5/Data-Preparation-Blending/Calculating-Week-Number-using-DateTimeFor...

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")

which lines up with ISO which is perhaps the most accepted standard.

Ben

[cstouwie]

I have week numbers and year number in separate columns in my data-set and I was needing to convert that directly to the Monday Start Date of that week (ISO Standard). I attempted to use the above solution but found some issues in the fact that it anchors it's calculation on Jan 1. So to try and resolve this I dug in the rabbit hole deep. I'd like to show you the summarized version of that journey of discovery I had:

I figured out that the Week Number standard most of us experience is tied to the ISO 8601 date and time standard that assigns each week a number on the Gregorian calendar. There are some years that have 53 weeks and some week 1's start with a previous years date then there is leap years that make things a little more confusing. So needing to know the methodology of how the ISO standard is developing week numbers, I searched and found the ISO standard wiki here which goes through all the possibilities for week 1 vs week 53 in a nice table which is ultimately tied to the day of week that Dec 31 falls on. Here is what it says:

"If 31 December is on a Monday or Tuesday it is in week 01 of the next year. If it is on a Wednesday, it is in week 01 of the next year in common years and week 53 in leap years. If it is on a Thursday, it is in week 53 of the year just ending; if on a Friday or Saturday it is in week 52 of the year just ending. If on a Sunday, it is in week 52 of the year just ending in common years and week 01 of the next year in leap years."

Pretty simple right? Yeah I didn't think so either. UNTIL searched "Calculating a date given the year week number 'Formula' " and found this website which had an excel solution to this problem:

=DATE([YEAR], 1, -2) - WEEKDAY(DATE([YEAR], 1, 3)) + [WEEKNUM] * 7

EUREKA! All I had to do was recreate this excel formula in Alteryx.

Attached is the rough solution I made myself in Alteryx to do just that.

I'm newer to Alteryx so there are probably 20 better ways to do what I did and make it cleaner than it is (sorry for the funky column names). Also, I wish I knew more about building macros since I'm sure this is a great candidate for a macro (Maybe someone wants to do that?!). OR EVEN BETTER YET, Alteryx wants to add this inherently into their DateTime Conversion tool??? (I guy can dream)