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.
on 09-11-201510:00 AM - edited on 07-27-202111:46 PM by APIUserOpsDM
When I'm out showing my customers some of the capabilities of Alteryx, I find that one question that always seems to get a "yeah!" response is "Any issues with date formatting?".
Now while this subject has probably been covered in the Alteryx KB elsewhere, I thought I would deal with a specific question that I just got this morning that can be the 'platform' for handling a lot of situations.
The question was "How do I get from a date like this...MM/DD/YYYY...to where I have the day integer, the month name, and the year in three different columns?"
Most Alteryx users know that there is a DateTime tool in the Parse category, but they also have found that all date formats aren't necessarily included there. What I have found that many users don'tknow is how flexible date formatting is when using a couple of the DateTime functions in the Formula tool.
These two functions are DateTimeParse and DateTimeFormat. The first one takes a String data type that represents a date and/or time and returns the value in a DateTime data type (you select the data type for it to return). The second one takes a Date, Time, or DateTime data type and returns a string in the format designated. The key to using either of these are the various 'specifiers' you can use within them. (You can find the list of specifiers in the DateTime Operations Help article.)
So for the above question, you are starting out with a string in a given format and basically want to get it into a string with a different format (and then separate it into columns).
The formula expression for this would be...DateTimeFormat(DateTimeParse([Date],'%m/%d/%Y'),'%d|%B|%Y')...where I am actually nesting the one function within the other. The 'inside' function takes the string and converts it to a Date type, and the 'outside' function converts it back to a String. As you can see, it doesn't matter what 'separators' you have for the dates...in this case I used the 'pipe' symbol because I like to use it in the Text to Columns tool (which is the tool I use to separate the date parts into columns), but many date formats use dashes, periods, or even spaces.
I use these two functions in combination like this all the time to get from one date format to another. And if you check out the Help article mentioned above, you will see there are specifiers for Day of the Week (which allows you to create a process that gives you only business days), 24-hour clock, AM/PM indicators, and even time zone.
So having worked with dates and times from a number of different data sources, I know how 'weird' some of them can be. These two Alteryx DateTime functions make bringing in that data and using it in analysis MUCH easier!
I've attached an example that deals with the specific question above. Hope this helps!