Calculating Days Between Dates or Adding Days in Alteryx

Calculating the number of days, months, or years between two dates in Alteryx is quick and easy with the use of the Formula Tool. The specific formulas needed will vary based on the exact situation. There are formulas to calculate between dates in columns or between a date field and today’s date with various units of measurement.

Calculating days between dates, adding days to dates, or calculating the difference between today's date and others can be done with Alteryx Formulas

We’ll explain how to use the Formula Tool in Alteryx to calculate the difference between two dates, along with the pre-requisites for date calculations, and how to split apart formulas into different steps so you don’t end up with one extra long formula that can be dificult to manage in the future.

Let’s jump in!

Alteryx Date Data Types

One of the areas that can be confusing for people new to Alteryx and data transformation software is the concept of data types. Even though a column in your data set may look like a date, it has to be defined with a specific data type as a pre-requisite for calculating the difference between dates.

Date fields in Alteryx are in the format of: YYYY-MM-DD , anything that differs from this will be read in as a string, or text. This means that any Date Formula we apply to a column in Alteryx has to be a Date Data Type. Attempting to Apply a Date formula to a String Data Type will result in a formula error.

The example below shows two date columns that are in a common Excel format.

Example of Alteryx workflow results showing date columns that are not in a compliant date format

If you click on the “Metadata” button on the left side of the workflow results panel at the bottom of Alteryx, we’ll see that these are actually V_String fields that would result in an error if we tried to calculate the difference between dates.

Viewing Alteryx meta data to determine data type

As a pre-requisite to applying date formulas, we converted them to a standard Alteryx Date Data Type and Format.

Example of Alteryx dates before and after being converted from a string to a date format

Converting strings to dates can be done in multiple ways, common methods include using the DateTime Tool and Multi-Field Formula Tool which you can learn more about in our guide to converting strings to dates in Alteryx.

Calculating the Difference Between Dates in Alteryx

To calculate the difference between two dates in Alteryx, add a Formula Tool from the Preparation section of the Alteryx Toolbar. Create a new column, and use the DateTimeDiff formula with the syntax: DateTimeDiff([Date1],[Date2],’days’). Columns are named in [square brackets] and the units are surrounded in single quotes.

When entering in the Dates, the first date named in the formula is the latter date, while the second date is the earlier date.

Units of measure could be, ‘days’ , ‘months’ or ‘years’

Calculating the difference between two dates in Alteryx with a DateTimeDiff formula

When you’re finished entering the formula, don’t forget to assign a numeric data type to the newly created column. This will ensure that the column sorts as expected and can be used in other calculations later on.

In this example, we used a Multi-Field formula tool to parse date columns to convert them into an Alteryx Date format and data type to apply calculations on them. However, you can also perform multi-step calculations directly in an Alteryx Formula Tool.

How to Return Today’s Date in Alteryx

To add a column for today’s date in Alteryx, add a Formula Tool from the Preparation section of the Alteryx Toolbar. Connect it to your workflow, and create a new column with the formula: DateTimeToday() and assign a Date Data Type. Use DateTimeNow() with a DateTime Data Type for today’s date and time.

The example below shows the DateTimeToday() formula added to an Alteryx Formula Tool.

Calculating today's date using a DateTimeToday formula in Alteryx

If the formula is correct, you will see the preview of today’s date in the Data Preview box at the top of the formula.

Creating Multi-Step Formulas in Alteryx

When working with dates or other formulas, it can be beneficial to split a formula into multiple steps. This avoids having to string multiple formulas together or nest them, making it easier to read and manage in the future. To add a second step to a Formula Tool, click on the blue + sign at the bottom of a formula configuration panel.

A new #2 formula will be added where you can reference fields created in prior steps.

In the example below we already pressed the + sign to add a second box. The second formula, references the name of the first step splitting a calculation for Today’s date with a calculation for the difference between Today and the Order Date Column.

Splitting a calculation into two steps using a multi-step Formula tool in Alteryx for easier readability

Steps can be re-arranged by clicking and dragging the three bars to the left of a formula field name. Steps are deleted by using the trash can icon to the right of a formula field name.

Adding Days to a Date in Alteryx

To add or subtract a specific number of days from a field in Alteryx, use a Formula Tool from the Preparation section of the Alteryx Toolbar. Create a new column, and use the DateTimeAdd formula with the syntax: DateTimeAdd([ColumnName],10,’days’) where 10 is the interval, and ‘days’ are the units.

Here’s the setup in a workflow after we converted string columns to date data types where we are adding 10 days to the Order Date Column.

Adding a set number of days to a date in Alteryx

Don’t forget to update the Data Type of the new column to Date to avoid errors at runtime. In addition to calculating the difference in days, you can also use months or years as units.

Converting Dates into Periods

When you need to extract a portion of a date in Alteryx and it’s already a Date Data Type, add a Formula Tool from the Preparation Section. Then use one of the formulas: DateTimeDay, DateTimeMonth, DateTimeYear with the syntax: DateTimeDay([ColumnName]) set the data type to a numeric type and run your workflow.

The results will be the specific part of the date formula parsed into a new column. The example below shows three new columns created with the formulas where the Day, Month, and Year are all parsed using formulas.

Parsing the date, month, and year from a date field in Alteryx using DateTime formulas

Alternatively, you can use the Alteryx DateTime Tool by defining the output format, but you would have to add 3 separate tools if you wanted to extract the day, month, and year where as the Formula Tool can embed 3 formulas into a single tool providing for a cleaner easier to read workflow.

Conclusion

Working with dates in Alteryx can be quick and easy using the Formula Tool. The first step is ensuring that columns are defined with a date data type. Even if it looks like a date from other systems, like Excel it has to be in a specific YYYY-MM-DD format to apply date formulas to it without receiving an error.

Once it’s in a date format you can use the Date set of Formulas. DateTimeDiff will calculate the difference between two dates. Today() will return today’s date as of workflow runtime. DateTimeAdd will add a specific number of days, months or years to a date, and other DateTimeDay formulas can convert a date into the number of days.

Formulas can be nested together or combined, you can also split formulas into multiple steps to make it easier to follow and maintain in the future.

Scroll to Top