When working with time-based data in Tableau, calculated fields unlock a powerful layer of flexibility and insight. Whatever you may be building a dashboard for, understanding how to manipulate dates is essential. I found them a little tricky to wrap my head around at first, so writing a blog helps me to consolidate my knowledge on them, and hopefully this helps someone else too!
In this post, I will explore some of the most useful Tableau date functions - DATEADD, DATETRUNC, DATEPART, DATEDIFF, DATETIME, DATEPARSE and ISDATE - and how they can elevate your analysis.
Why Date Calculations Matter
Dates are rarely useful in their raw form. We often need to:
- Compare performance across time periods
- Aggregate data at different levels (day, month, quarter)
- Create rolling metrics (e.g. last 7 days, year-to-date)
- Align irregular time series
This is where calculated fields come in.
1. DATEADD
The DATEADD function allows you to add or subtract time intervals from a date.
Syntax:
DATEADD('date_part', interval, date)
Example:
DATEADD ('month', 1, [Order Date])
This adds one month to the [Order Date].
When to use:
- Creating future or past reference dates
- Building rolling windows (e.g. 30 days ago)
- Forecasting
You can also subtract the interval (number) in order to find previous time periods. For example, to calculate "Last 7 Days":
[Order Date] >= DATEADD('day', -7, TODAY())
2. DATETRUNC
DATETRUNC is used to truncate a date to a specific level of detail - this allows us to control the granularity of a date field.
Syntax:
DATETRUNC('date_part', date)
Example:
DATETRUNC('month', [Order Date])
This returns the first day of the month for each date. The ‘date_part’ bit is essentially asking which part of the date to aggregate to. In this case, month.
When to use:
- Aggregating data at month, quarter, or year level
- Creating consistent time buckets
- Supporting time-series visualisations
Why It Matters:
Without truncation, Tableau may treat each date as unique. Using DATETRUNC ensures proper grouping.
3. DATEPART
DATEPART pulls out a specific component of a date as a number.
Syntax:
DATEPART('date_part', date)
Example:
DATEPART('year', [Order Date])
Returns the year as an integer (e.g. 2024).
Common Variations:
- 'month' = 1-12
- 'weekday' = 1-7
- 'quarter' = 1-4
When to use:
- Creating filters (e.g. only Mondays)
- Building custom groupings
- Supporting calculations like seasonality analysis
DATEPART and DATETRUNC can seem similar at first because of their common syntax. The difference is the type of value they return. DATEPART returns a number that is extracted from the full date in the output. For example, if ‘month’ was specified in the syntax, DATPART would return ‘1, 2, 3…12’. DATETRUNC returns a full date, but rounded down to a chosen level of detail, whilst still keeping the other parts of the date.
Side by side example: If [order date] = 22nd September 2024
DATEPART (‘month’ [order date]) - 9
DATETRUNC (‘month’, [order date]) - 01 September 2024
So, use DATEPART when you want a number, and use DATETRUNC when you want a date for grouping.
4. DATEDIFF
DATEDIFF calculates the difference between two dates in a specified unit.
Syntax:
DATEDIFF('date_part', start_date, end_date)
Example:
DATEDIFF('day', [Order Date], [Ship Date])
This returns the number of days between the two dates.
When to use:
- Delivery times
- Customer lifecycle (e.g. days since first purchase)
- Time between events
5. DATETIME
DATETIME converts a value into a datetime format (date + time).
Syntax:
DATETIME (expression)
Example:
DATETIME ([Order Date])
When to use:
- When your field is a string or date and you need time included
- When you’re standardising formats across fields
6. DATEPARSE
DATEPARSE converts a string into a date using a specified format.
Syntax:
DATEPARSE ('format', string)
Example:
DATEPARSE ('dd/MM/yyyy', "22.September.2001")
Converts the string date into a proper date
When to use:
- Cleaning messy data
- Importing non-standard date formats
- Working with CSV/text data
7. ISDATE
ISDATE returns True or False depending on whether a value can be interpreted as a date.
Syntax:
ISDATE (string)
Example:
ISDATE ("2001-09-22")
Returns:
- TRUE → valid date
- FALSE → invalid date
When to use:
- Data validation
- Filtering out bad records
Best Practices
- Use DATETRUNC for aggregation, not DATEPART
- Be mindful of data types - date vs datetime can affect results
- Test calculations with filters to ensure expected behaviour
- Name calculated fields clearly for maintainability
Final Thoughts
Calculated date fields are foundational to effective Tableau analysis. By mastering these functions, you can transform raw timestamps into meaningful, actionable insights.
If you’re building dashboards that rely on time-based data, these functions aren’t just useful - they’re essential.
Happy analysing!