cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Week start date from Week number and Year

Asteroid

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

Pulsar

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!

Alteryx Partner

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.

Highlighted
Meteoroid

Hey all,

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

Labels