The Tableau DATEADD is one of the date functions that will add a user-specified number of intervals to the given date based on the date_part (first) argument.
For example, if you pass 5 as the second argument and the year as the first argument, the DATEADD function adds five years to the given date, i.e., third_argument. This article shows how to use this Tableau DATEADD function and add different date_part values to build or predict new dates with examples.
The syntax of the Tableau DATEADD function is as shown below.
DATEADD(date_part, interval, date)
In the above DATEADD syntax,
- The date_part (first) argument accepts iso-weekday, iso-week, iso-quarter, iso-year, year, day, month, week, quarter, weekday, hour, minute, second, dayofweek, etc.
- In the Tableau DATEADD function, the interval argument accepts positive and negative integer values and adds this value to the existing date date_part.
- The third argument is the actual date that we want to use. For instance, date_part = ‘month’, interval = 4, and date = 01/01/2020. It means the DATEADD function adds four months to 01/01/2020, and the output is 01/06/2020.
Tableau DATEADD Function Examples
To demonstrate this Tableau DATEADD Date function, we use a Hire Date column in the Employee table and try to increment or add all kinds of intervals to the date by experimenting with the date_part.
First, we must create a calculated Field to use this Tableau built-in DATEADD function because we have to write an expression. To do this, right-click the empty space within the Measures shelf to select the Create Calculated Field.. option.
Add 10 Years to the Date
In this Tableau DATEADD function example, we add ten years to the Hire Date. To do this, please write the expression below with the first argument as year, 10 as the interval, and HireDate as the third argument. Since the first argument is a year, the code below will add ten years to the HireDate column.
DATEADD('year', 10, [Hire Date])
Next, let me add that calculated field to the table. If you look at the output, you can see the 10 Year difference. Remember, when you add a date field to the Rows shelf, it may select the YEAR(AddYears). So, please click the down arrow beside the YEAR(AddYears), checkmark the Exact Date, and Change it to Discrete.
From the above Tableau image, you can see that we have formatted the text. To do so, right-click the Date cell and click the format or use the format menu. For more information, refer to the Date Functions and the Other Functions articles.
Tableau DATEADD function to Subtract 5 Years from the Date
The interval argument also accepts the Negative values—positive means for adding, and Negative means subtracting date_part from the Date. For example, let me Edit the existing AddYears calculated field and replace the interval value 10 with -5. It will remove five years from the HireDate. Please check it from the below image.
DATEADD('year', -5, [Hire Date])
Add 5 Days to the Date
If you want to add five days to the Hire date, then, like the above, create another calculated field, AddDays with the day as the first, 5 as the second, and HireDate as the third argument.
DATEADD('day', 5, [Hire Date])
Similarly, to subtract 30 days, use the following.
DATEADD('day', -30, [Hire Date])
Use this same technique to add months, weeks, hours, minutes, seconds, etc. Remember, you must create a new calculated field for each calculation with appropriate date_part, interval, and date arguments.
Tableau DATEADD function to add 1 Month to the Date
To add the month number, use the below expression
DATEADD('month', 10, [Hire Date])
Add 2 Hours to the Date and Time
To add the Hours to the Hire date and time, use the below expression
DATEADD('hour', 2, [Hire Date])
Tableau DATEADD function using parameter
If you want to allow the users to provide date_part to the DATEADD function, create a parameterized report with a list of date_part items. To do so, right-click on the Measures shelf empty space and select the Create Parameter option to open the following window.
Let me use the ParamDatePart as the Name, select the String as the Data type, and add a list of parameter values representing the date_part of the Tableau DATEADD function, i.e., year, month, day, hour, minute, second, and week. Next, Edit the AddYears or create a new calculated field with parameter as the first argument, 10 as the second, and HireDate as the third.
DATEADD([ParamDatePart], 10, [Hire Date])
Next, click the down arrow beside the ParamDatePart parameter and choose the Show Parameter option from the context menu. By default, it adds 10 hours to the HireDate.
Please use the drop-down list to change the date_part.
To give more control to the user, let me create a new parameter named ParamInterval of integer type with minimum 1 and maximum value of 100. Next, edit the calculated field and add the below code.
DATEADD([ParamDatePart], [ParamInterval], [Hire Date])
Now, you can use the drop-down list to change the date_part of the Tableau DATEADD function and the slider to change the interval.
