Converting Strings (Text) to Dates in Alteryx

Most data sets that are imported into Alteryx are not in the standard ISO date format that Alteryx requires to apply date calculations. You’ll need to first convert or parse string (text) and convert it to a date so that Alteryx can interpret the field as a date data type to avoid errors and perform calculations.

Converting string or text to dates in alteryx can be done with a DateTime tool, Formula tool, or multi-field tool.

Converting strings to dates in Alteryx can be done a number of ways. It’s a highly useful skill to keep in your toolbox as most dates are imported into Alteryx in a non-standard format that will cause calculation errors when applying different date formulas to them. We’ll explain a few different ways to convert strings to dates.

Let’s dive in!

Alteryx Date Conversion Errors

Alteryx will return a “Value” is not a valid date or time when it fails to apply a calculation or convert a data type from a string to a date. The results of the formula will often be a column of null values. To correct the error you have to first convert the column to a date data type prior to performing the next operation.

The example below shows a “Value” is not a valid Date or Time conversion error in the workflow results window at runtime. This is because the 03/01/2024 style date format is not registered as a date data type in Alteryx. The only date format supported is 2024-01-03. Any other format will be interpreted as text.

Example of a date conversion error in Alteryx when a field is not a valid Date or Time

Conversion errors rarely result in a hard stop of a workflow, but will show up in your workflow run results and the resulting column will show blank or null values.

Results of a date conversion error in Alteryx showing a null column

Typically an error such as this doesn’t stop a workflow but results in inaccurate results or causes a tool downstream to fail causing the workflow to error out.

Identifying Alteryx String vs Date Data Types

The easiest way to determine if a date has a text or date data type is to look at the formatting. Only dates in the format of: YYYY-MM-DD are interpreted as a date data type, all other formats are strings. You can also click on the Metadata button in the workflow results to toggle to the data type view.

The example below shows an Alteryx date format in the left Date column, with a more common Excel style format in the middle called Common Date.

Example of a date column and a string column next to eachother showing a date

When you click on the Metadata button, you can also see the data types of the columns in your data set which comes in useful for identifying dates, but also for other column data types.

The metadata view of Alteryx showing date and V_String data types of two date columns

When a column comes in as a string, as it often does from Excel or CSV files it will need to be converted to a date format and a date data type.

Converting Strings to Dates with Alteryx DateTime Tool

Use the DateTime Tool from the Parse section of the Alteryx toolbar to convert strings to Date/Time format. Configure the tool by selecting a column, naming the new output column, and selecting or creating a custom a template format for the incoming data.

The illustration below shows the 3 primary sections of the Alteryx Date/Time tool when converting a string to a date.

  1. Select String to Date/Time format. (The tool can also be used to format dates in the other direction)
  2. Name the new output column. This method does not update a column in place, it only creates new ones.
  3. Choose a template of the starting data or specify a custom format at the bottom of the configuration panel.
How to configure the DateTime tool in Alteryx for string to date format conversion

When Alteryx converts text to a date it requires a template to understand what the starting format is. This is necessary because some dates could be read as 02/01/2024 where it’s either February 1st or January 2nd depending on the incoming date standard being used in the data.

A common next step when using the DateTime tool to convert text to dates is to use a Select tool to drop the original column, and rename the newly generated one from the DateTime tool.

The setup will look something like the following.

Adding a select tool to adjust the output of a DateTime tool

The original column is unselected while the new column is renamed to match the old column.

Converting Strings to Dates with Alteryx DateTimeParse Formula

The DateTimeParse formula in Alteryx converts a field from a string format to a date format. The syntax to use the formula is: DateTimeParase([FieldName],’incoming format’) where the incoming format is the pattern of text that needs to be parsed usually something like: ‘%m/%d/%Y’ to convert 12/31/2024.

The abbreviations are shortcut specifiers that tell Alteryx how the incoming date is formatted for it to accurately parse it. You can learn more about the specifiers available from the following Alteryx Article. DateTime Functions (alteryx.com)

Using a DateTimeParse formula to convert a string to a date in a formula tool

Alteryx also has a formula called “ToDate” that allows you to temporarily convert a string to a date for the sake of embedding a conversion temporarily directly into a formula tool which won’t impact the column data type.

Be aware that when using a formula tool, you will have to create a new column with a Date data type. You cannot update the data type of an existing column with a regular formula tool.

However, you can with a multi-field formula tool.

Converting Multiple String Fields to Dates with a Multi-Field Formula

The multi-field formula allows you to apply a single formula to multiple fields at one time. You can use it to convert multiple string columns to date columns and change the data type in a single step. Multi-Field formulas are available in the preparation section of the Alteryx toolbar.

There are 3 steps to configuring the multi-field formula.

  1. Select the columns to update. The list is filtered by started data type with the dropdown box at the top.
  2. Uncheck “Copy Output Fields and Add” if you want to update the existing columns without creating new ones. Also select the Change Output Type to “Date” to convert as string to date.
  3. Apply a DateTime Parse Formula using the [_CurrentField_] name in place of a regular field name. You can expand Current Field under variables and double click it to insert it into your formula.
Converting multiple columns to date formats at one time using a multi-field tool

When the workflow runs, the selected columns at the top of the configuration panel will be updated to the standard Alteryx date format along with converting them to a date data type in a single step.

The multi-field formula tool will not highlight the formula text with colors to know that the syntax is correct. This is a limitation of the multi-field tool at this time and the formula should execute without an error. If you’re unsure of the Syntax you can try using a regular formula tool with a specific field name to test the syntax of the rest of the formula.

Converting Alteryx Date Formats

It’s worth noting that you can also go in the other direction and convert date data types in Alteryx to other string formats. You can learn more about the process in detail from our Alteryx Guide to Changing Date Formats to learn more.

Conclusion

Alteryx is a highly flexible tool and typically provides several different methods to achieve a given task. Converting strings to dates is no exception. You can easily convert a string to a date using the Date/Time tool but it will generate a new column that often has to be renamed. Alternatively you can use a Formula tool to make the date conversion which is useful when it’s part of a larger calculation. Finally, you can use a multi-field tool to combine several steps into one where you parse text into a date and update the data type to date in a single step for one or multiple columns at a time.

Scroll to Top