How to Change Date Formats in Alteryx

Alteryx provides users with several ways to convert date formats. Users can use the DateTime tool for a no-code method of changing date formats, or they can apply custom formulas that give them greater control over the final formatting of a date output.

Date formatting conversions in Alteryx can be done with a Formula Tool, Multi-Field Formula or the Date/Time tool

Alteryx requires an ISO 8601 date format for representation al calculations to be able to change date columns that’s notated as YYYY-MM-DD, it makes identifying date data types easier in the workflow results, but is not appropriate for all desired outputs. Alteryx gives people a few different was to convert date formats including the date time tool and writing custom formulas.

Let’s jump in!

Understanding Date Formats in Alteryx

To fully understand how to work with date fields in Alteryx it’s important to first understand how Alteryx works with Data Types. Each column in Alteryx is assigned a Data Type that tells Alteryx how to work with the column. For example, numeric columns can be added together. Text or String columns can be concatenated. etc.

Dates have their own specific Date Data Type in Alteryx that must be applied to be able to perform date calculations, such as determining the number of days between two dates or adding a month to a specific date.

However, Alteryx represents all dates using the ISO 8601 standard, YYYY-MM-DD or 2024-03-31 style format.

If you ever see a date in the workflow results that is not in this format, most likely the date column is actually a string (text) data type.

So this means that when we convert a date column to another format, we need to do two things.

  1. Change the output of a date column’s formatting
  2. Change the column from a date data type to a string data type.

It also means that for the following steps to work, you have to start with a date data type. You cannot apply the following methods to a string data type and adjust the date formatting, as it is not really a date column as defined by the Alteryx data type.

Identifying Field Data Types in Alteryx

The fastest way to determine a date type of an existing field in Alteryx is to change the workflow results view from “Data” to “Metadata” using the selection options on the upper right corner of the workflow results panel. Once toggled, you will see the field types and other information about your data instead of the results.

By default, you will see the following view of the workflow results at the bottom of Alteryx Designer. It shows two columns, Date is in the standard Alteryx ISO format, the Common Date column is formatted like most people are used to seeing in Excel or other systems.

Screenshot of the workflow results page showing dates in two common formats but different data types

When we flip to the Metadata view, we can see that the first column has a Date data type. The Common Date field has a V_String (text) data type.

Viewing the data types of current columns using the Meta Data view in workflow results pane

This is important because if we wanted to apply date calculations to a column in Alteryx we would be able to do so only on the Date field. Applying date formulas to the Common Date field would result in an error due to a data type mismatch.

Formatting Dates with the Alteryx DateTime Tool

To change the format of a Date field in Alteryx, connect the DateTime tool from the Parse section of the Alteryx Toolbar. Configure it to convert “Date/Time format to string”, name the new column, and select a present date output format or define a custom one.

The DateTime tool is located on the left side of the Parse section of the Alteryx Toolbar.

The DateTime tool is located on the parse section of the Alteryx toolbar

For it to work appropriately, the starting column must be in a Date or DateTime format.

To configure the DateTime tool follow these steps:

  1. Select Date/Time Format to String
  2. Specify the name of the new output column. The DateTime tool does not update existing columns, it only creates new ones.
  3. Select the desired date format for the new column.
Illustration of how to configure the Date/Time tool in alteryx for date to text conversion

The final step when working with the DateTime tool is typically to add a Select tool so you can rename the name column, and drop the old one from your data set to avoid duplication of columns in your final output.

Changing Date Formats with the Alteryx Formula Tool

For more precise control over converting date formats in Alteryx, you can use a Formula tool to create a new column, usingthe syntax: DateTimeFormat([Date],’%m/%d’%Y). The first part is the formula, the middle part defines the target column, and the latter is the desired output format.

The setup for the formula will look like the following:

Using the DateTime Format to convert dates to a MM/DD/YYYY format

Notice that the date format output is presented using date specifiers. These are similar to the ones that can be used in Excel or other systems, but not all common abbreviations are supported. You can find the entire list of date specifiers from Alteryx.

The final thing to make sure of is that the data type is defined as a string data type. If you attempt to output the new format as a date that’s not in the standard Alteryx ISO format the results will be an error.

One drawback of the first two methods is that they generate new columns. You can’t change the data type of an existing column with a regular Formula tool. However, you can with the Multi-Field tool.

Changing Date Formats and Data Types with the Multi-Field Tool

The Multi-Field tool, available in the Preparation section of the Alteryx toolbar allows developers to apply a single formula to multiple columns at one time, and lets you change the output data type of existing columns. It’s a massive time saver when updating multiple date column formats in one step.

To configure the Multi-Field tool follow these steps:

  1. Select the field or fields you want to apply a formula too. Use the Select dropdown to change between pre-defined data types such as seeing all date or all text fields at one time.
  2. Uncheck Copy Output Fields and Add to update the existing columns in place. Otherwise new columns will be generated. Define the data type of the columns you want after the formula is applied.
  3. Under the Variables tab there is a special field name called _CurrentField_ that acts as a place holder for the column names selected up above. Use this in a formula instead of a specific column name.
  4. Define the DateTimeFormat formula like you would a regular Formula tool, but use the _CurrentField_ syntax instead of a specific column name.
Using a multi-field formula tool in Alteryx to convert selected current fields to a date time format while adjusting the data type in one step without creating new columns

When using the Multi-Field formula tool, the formulas won’t become color coded like they do in a regular formula tool. This is regular behavior as the Multi-Field tool does not support the functionality, it does not mean that your syntax is incorrect or that the formula will error out.

Conclusion

Alteryx provides users with several methods to change the data format of their data, but they first have to have data assigned to a date data type for date formulas to work. You can either use the DateTime tool for a no-code method of date format conversions, but it can be somewhat limited.

For more control over the process, you can use a regular Formula tool with the DateTimeFormat formula to create a new column where a date has been formatted. You can also nest this formula in with other formulas, such as ToDate or DateTimeParse to start with a date format that is being read in as a string in a non-standard Alteryx ISO 8601 format.

When updating multiple columns or to use as few tools as possible, use the Multi-Field formula. You can apply one formula to many columns and convert the data type of a column in one step without

Scroll to Top