This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!Submit Feedback
The link to last week’s challenge (challenge #20) is HERE. For this week’s challenge we have an easier one that requires the reformatting date fields. The input data has very poorly formatted month and year information that will require you to build some logic to make it usable.
Use Case: A company needs to reformat a month/year flag in a data asset they received for a consulting project.
You will need to take the Date field provided and separate it into fields for the Month and Year. For example, F07 should become 2 columns where month is Feb and year is 07.
spoiler alert: switch + IF
I took a slightly different route which will may be a little quicker on a large data-set
Solution! Ended up having to take the same track as @SeanAdams with the Text Input + Join for month names, but I also like @MarqueeCrew solution with the Switch formula, will have to log that away as well.
Another potential tool improvement request (unless, again, I am missing something that already does this): Would love to see enhancements to DateTimeFormat that allow you to indicate Month and day of week in the same manner Excel does when you use the Text formula, as words/partial words instead of numbers... For example, "MM" = 02, "MMM" = Feb, "MMMM" = February... or "dd" = 07, "ddd" = Wed, "dddd" = Wednesday. Anyone else feel this would be useful? Certainly would have streamlined this challenge, but I can see it having useful application in a variety of situations where standard DateTime formats as values are just not sufficient.
Agree with you about the need to relook some of the data parsing & conversion functionality @NicoleJohnson - there's a few ideas in the Ideas section that you could add to.
@MarqueeCrew suggested something similar to you here: https://community.alteryx.com/t5/Alteryx-Product-Ideas/Alteryx-making-dating-easier/idi-p/20625
I suggested refactoring data-time treatment to make it the same throughout - in some places month is "%b" (like DateTimeParse), in some its "Months" (like DatePart) and in some it's "Mon" (like the date time tool). I think this is an indication that these were built at different times as thinking was evolving, and it's probably time to refactor the treatment of DateTimes thoroughly and make this consistent and simple.
Have a look - and if you agree with any of these two ideas (or the raft of other dateTime related ideas in the Ideas section) - give them a star and create some buzz around them by talking about them to others so that we can get them up the priority queue for future release.
Another fun solution
I didn't fancy writing out a lengthy formula to calculate the months so I took a slightly different route...