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?!). 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). Use a conversion function to convert numbers to strings or strings to numbers. =DATE(, 1, -2) - WEEKDAY(DATE(, 1, 3)) + * 7ĮUREKA! All I had to do was recreate this excel formula in Alteryx.Īttached is the rough solution I made myself in Alteryx to do just that. A conversion function converts one data type or format to another data type or format. UNTIL searched "Calculating a date given the year week number 'Formula' " and found this website which had an excel solution to this problem: Pretty simple right? Yeah I didn't think so either. ![]() 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." 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 it is on a Wednesday, it is in week 01 of the next year in common years and week 53 in leap years. "If 31 December is on a Monday or Tuesday it is in week 01 of the next year. 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. ![]() 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. ![]() 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. I'd like to show you the summarized version of that journey of discovery I had: So to try and resolve this I dug in the rabbit hole deep. I attempted to use the above solution but found some issues in the fact that it anchors it's calculation on Jan 1. 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).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |