Week start date from Week number and Year

[poojitha4]

Hi,

I have two columns, week number and Year, I need to find the start date of the week(Monday) for that week. Any ideas or leads will be highly appreciated.

Here is a sample

YEAR	WEEK
2016	39
2017	21
2015	49
2017	8

Thank you,

Poojitha

[danrh · Accepted solution]

A little bit of a hack solution:

I create records for the 8 days prior to the week number multiplied by 7, filter down to just Mondays, and grab the biggest date.  You might have to play with the math a little to make sure the week calculations are landing correctly.

Hope it helps!

[derekbelyea · Accepted solution]

Here is a second method that also works and uses a similar approach. 

The workflow in Method 2 can be further simplified. Some of the formula fields are included to confirm the logic.

[cstouwie]

Hey all, 

Resurrecting this thread....

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)

Hope someone enjoys this....