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
Thank you,
Poojitha
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!
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.
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....
