How to Manipulate Dates in Alteryx

Whether you work with data as a hobby or as your career, it is all but certain that you will encounter dates within a dataset at some point during your exploration. From ensuring that the dates are in a specific format and recognized as a Date data type, all the way to utilizing dates to help clean the dataset or set up an analysis, Alteryx provides many tools and formulas that are specifically designed for dates. In this blog, we will cover the Date data type, Alteryx tools used to process dates, date functions, and advanced applications.

Getting started

Dates can show up in your dataset in a variety of formats. For example, June 01, 2023, could be encoded like  “06/01/23”, “2023-06-01”, “Jun 1, 2023”, “Thurs, Jun 1, 2023”, etc…  In the United States we are usually taught to write dates as “06/01/2023”, however, most data programs, including Alteryx and SQL, prefer the dates in what is called ISO format, “2023-06-01”.  Therefore, your first goal when working with dates is to get it into the format that works best for your end product.

Tips for Earning Your Alteryx Designer Core Certification

Exploring the toolbox

Alteryx provides a tool that allows you to easily convert a string to a date format or convert a date format into a string called the DateTime tool within the Parse tool palette.

Within this tool, Alteryx has you select the column you want to convert, determine the new field output and push it into a new column. When converting a string to a date format, the tool will have you select the format of the incoming string, and it will convert it to the ISO date format.  For example, we have a date formatted as “06/01/2023”, which is a string. To get it into the ISO date format, you would first drag in the DateTime tool onto the canvas, then select “MM-dd-yyyy” from the list.

Live Tableau Training
May 14 – 17, 2024

Learn directly from industry-leading experts, access hundreds of post-training resources, and more.

Book Your Spot

Following these steps, Alteryx will output a new column with the date in ISO format, “2023-06-01”. Below we’ve included a couple of example images to better demonstrate this process.

When converting a date to a string, you will select the format of the string.  With “2023-06-01” as an input, you could select the option of “Month, dd, yyyy” and the tool would output the date as “June 01, 2023”.

9 Quick Alteryx Tips to Optimize Your Data Workflows

If none of the options in the predetermined list meets your conversion needs, you also have the option to specify a custom format using Alteryx’s extensive list of datetime functions.

Formulas to the rescue

When working with any data type, the ability to clean, update and add values to a current dataset is a must; dates are no expectation to this rule. That’s why Alteryx has an entire section of formulas just dedicated to helping you organize and update date values. The formula tool can be found under the “Preparation” tab at the top of the user interface. To use the formula tool, you simply drag it onto the canvas and connect it to a data stream. Once we’ve got our input connected and our formula tool selected, you’ll be greeted by the following menu on the left:

To see all of the different formulas you can choose from, you’ll just want to click on the blue “fx” button on the left-hand side. Alteryx provides many different types of formulas broken down into subcategories, but for this article, we’ll be focusing our attention on the formulas housed under the “DateTime” section:

You can choose whichever formula helps you accomplish your cleansing or analysis goals, and hovering your mouse over any of them will generate a small popup that provides a brief description of what that formula does. We’ve listed a refined selection of some of our frequently used formulas below with a brief explanation of what they do:

• • DateTimeAdd(dt,i,u) – (dt = the column containing your date data or a static date; i = an integer representing how many days/months/years, etc. you want to add to the dates; u = the unit or specifier representing days/weeks/months, etc. as a string value).  For example, DateTimeAdd(‘2023-06-01’,1,’months’) would produce the result ‘2023-07-01’.  More info on this can be found here on Alteryx’s site. We’ve also posted an example of this below:

• • DateTimeNow() – This function requires no parameters and allows you to pull the current date. This can be useful if you want to dynamically add the current date every time you run a workflow or for implementing logic to always pull the most current data from a database using the dynamic input tool.

• • DateTimeDiff(dt1,dt2,u) – (dt1 = the first date you want to use as a comparison; dt2 = the second date you want to compare against the first date; u = the unit of difference you want to have returned (days/weeks/months)). This value is returned as an integer that can be used in later calculations. For example – DateTimeDiff(‘2023-06-01’,’2023-05-01’,”months”) = 1

Taking it a step further

Now that we’ve covered some of the essentials when it comes to working with date data types in Alteryx, let’s discuss some applications that these tools can have when we’re developing. In the previous section we touched on the DateTimeNow() formula function and how we can use it to pull the current date whenever we run a workflow; to take this a step further, let’s say we have a workflow that combines historical data with newly published data and we want to do some processing on the new data before we append the two sets together.

We can use the DateTimeFirstOfMonth(), which performs the exact same function as DateTimeNow() but pulls the first of the current month as opposed to the current day, function in a filter. This will siphon off our new data into a separate run that has its own set of tools and formulas applied to it before it is added back on to our original dataset. This allows us to update our data in a dynamic way that requires no intervention from us as the developer, allowing for automation. 

Receive More Free Data Tutorials Like This Every Week

Let’s stay in touch:

Email(Required)

Δ

Another use case we come across often is when querying a database for recent data. The DateTimeNow() function can help us automate a data pull with the Dynamic Input Tool. We can use the value we get from this formula to update a SQL query WHERE clause to pull the date that we receive as an output from this formula, allowing us to automatically run a workflow that only pulls current relevant data with the click of a button.

Conclusion

In this blog we’ve covered the basics of the Date data type, some useful formulas that Alteryx provides to help us manipulate those dates, and use cases for taking those formulas to the next step. We hope this high level overview of dates will give you a good headstart and comfort level when diving into working with dates! 

Best of Luck!
Ariana & Nick