Date calculations (DATEDIFF, DATEADD) in Tableau - Step-by-Step Guide

Introduction

Date calculations help you find differences between dates or add time to dates. This is useful when you want to see how much time passed or predict future dates in your data.

When you want to find how many days passed between order date and delivery date.

When you need to add 7 days to a start date to find a deadline.

When you want to compare sales month over month by adding or subtracting months.

When you want to calculate the number of years between a customer's signup date and today.

When you want to shift dates in your data to analyze trends over different time periods.

Steps

Step 1: Click

- Analysis menu

A dropdown menu appears with calculation options

Step 2: Select

- Create Calculated Field

A dialog box opens to enter a new calculation

Step 3: Type

- Calculation editor

You can write your date calculation formula

💡 Use DATEDIFF to find difference between two dates, e.g. DATEDIFF('day', [Start Date], [End Date])

Step 4: Type

- Calculation editor

You can write your date addition formula

💡 Use DATEADD to add time to a date, e.g. DATEADD('month', 1, [Order Date])

Step 5: Click

- OK button in the calculation editor

The new calculated field appears in the Data pane

Step 6: Drag

- New calculated field to Rows or Columns shelf

The visualization updates showing the date calculation results

Before vs After

Before

Data shows order dates and delivery dates as separate columns without any calculation

After

A new column shows the number of days between order and delivery for each record

Settings Reference

Date Part

📍 Inside DATEDIFF and DATEADD functions in calculation editor

Defines the unit of time to calculate difference or add (e.g., days, months)

Default: day

Start Date

📍 First date argument in DATEDIFF or DATEADD

The date to start calculation from

Default: None

End Date / Number

📍 Second date argument in DATEDIFF or number to add in DATEADD

The date to compare to or number of units to add

Default: None

Common Mistakes

Using DATEDIFF with wrong date part like 'days' instead of 'day'

Tableau requires exact date part keywords like 'day', not plural

Use correct date part keywords such as 'day', 'month', 'year' exactly as Tableau expects

Using DATEADD with a negative number but forgetting to put minus sign

Without minus sign, DATEADD adds time instead of subtracting

Use negative numbers to subtract time, e.g. DATEADD('month', -1, [Date])

Mixing date fields and non-date fields in calculations

DATEDIFF and DATEADD require date fields for correct results

Ensure both fields used are date type or convert text to date before calculation

Summary

DATEDIFF calculates the difference between two dates in units like days or months.

DATEADD adds or subtracts a specified number of time units to a date.

Use exact date part keywords and correct data types for accurate results.

Practice

1. What does the Tableau function DATEDIFF('day', #2024-01-01#, #2024-01-10#) return?

easy

A. 10

B. 11

C. 9

D. Error

Solution

1. Step 1: Understand DATEDIFF parameters

   DATEDIFF counts the number of boundaries crossed between two dates in the specified unit, here 'day'.

2. Step 2: Calculate days between 2024-01-01 and 2024-01-10

   From Jan 1 to Jan 10, there are 9 full days difference, but DATEDIFF counts the number of day boundaries crossed, which is 10 (Jan 1 to Jan 2 is 1, ... Jan 9 to Jan 10 is 9, plus the starting boundary counts as well).

3. Final Answer:

   10 -> Option A

4. Quick Check:

   DATEDIFF('day', start, end) counts days crossed = 10 [OK]

Hint: DATEDIFF counts boundaries crossed, not total days [OK]

Common Mistakes:

• Counting both start and end dates as full days
• Confusing DATEDIFF with DATEADD
• Using wrong unit like 'month' instead of 'day'

2. Which of the following is the correct syntax to add 3 months to a date field [Order Date] in Tableau?

easy

A. DATEADD([Order Date], 3, 'month')

B. DATEADD('3 months', [Order Date])

C. DATEDIFF('month', [Order Date], 3)

D. DATEADD('month', 3, [Order Date])

Solution

1. Step 1: Recall DATEADD syntax

   DATEADD takes three arguments: date part as string, number to add, and the date field.

2. Step 2: Match correct argument order

   DATEADD('month', 3, [Order Date]) matches syntax: DATEADD('month', 3, [Order Date]). Others have wrong order or wrong function.

3. Final Answer:

   DATEADD('month', 3, [Order Date]) -> Option D

4. Quick Check:

   Correct DATEADD syntax = DATEADD('month', 3, [Order Date]) [OK]

Hint: DATEADD('unit', number, date) is the correct order [OK]

Common Mistakes:

• Swapping argument order
• Using DATEDIFF instead of DATEADD
• Passing units as part of number argument

3. What is the result of this Tableau calculation?
DATEDIFF('week', #2024-01-01#, DATEADD('day', 15, #2024-01-01#))

medium

A. 2

B. 3

C. 1

D. 0

Solution

1. Step 1: Calculate DATEADD('day', 15, #2024-01-01#)

   Adding 15 days to Jan 1, 2024 results in Jan 16, 2024.

2. Step 2: Calculate DATEDIFF in weeks between Jan 1 and Jan 16

   Weeks crossed: Jan 1 to Jan 8 (1 week), Jan 8 to Jan 15 (2 weeks), Jan 15 to Jan 16 does not complete another week. So total 2 weeks difference.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   DATEDIFF('week', start, end) counts full weeks crossed = 2 [OK]

Hint: Add days first, then count weeks crossed [OK]

Common Mistakes:

• Counting partial weeks as full weeks
• Mixing up order of DATEADD and DATEDIFF
• Using wrong date formats

4. You wrote this Tableau formula but it gives an error:
DATEADD('day', '5', [Ship Date])
What is the problem?

medium

A. The date field [Ship Date] must be a string

B. The date part 'day' should be in uppercase

C. The number of days should not be in quotes

D. DATEADD cannot add days, only months

Solution

1. Step 1: Check argument types in DATEADD

   DATEADD expects the second argument as a number, not a string.

2. Step 2: Identify error cause

   Using '5' (string) instead of 5 (number) causes a type error.

3. Final Answer:

   The number of days should not be in quotes -> Option C

4. Quick Check:

   Number argument must be numeric, not string [OK]

Hint: Numbers in DATEADD must be numeric, no quotes [OK]

Common Mistakes:

• Putting numbers in quotes
• Assuming case sensitivity for 'day'
• Thinking DATEADD only works with months

5. You want to find how many full quarters have passed between #2023-02-15# and a date 200 days later. Which Tableau formula correctly calculates this?

hard

A. DATEDIFF('month', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) / 3

B. DATEDIFF('quarter', #2023-02-15#, DATEADD('day', 200, #2023-02-15#))

C. DATEDIFF('quarter', DATEADD('day', 200, #2023-02-15#), #2023-02-15#)

D. DATEADD('quarter', 200, #2023-02-15#)

Solution

1. Step 1: Calculate the date 200 days after Feb 15, 2023

   Using DATEADD('day', 200, #2023-02-15#) gives the target date.

2. Step 2: Use DATEDIFF with 'quarter' to count full quarters passed

   DATEDIFF('quarter', start_date, end_date) counts how many quarter boundaries are crossed.

3. Step 3: Evaluate options

   DATEDIFF('quarter', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) correctly uses DATEDIFF with 'quarter' and correct date order. DATEDIFF('month', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) / 3 divides months by 3 but may give decimals, not full quarters. DATEDIFF('quarter', DATEADD('day', 200, #2023-02-15#), #2023-02-15#) reverses dates causing negative result. DATEADD('quarter', 200, #2023-02-15#) misuses DATEADD.

4. Final Answer:

   DATEDIFF('quarter', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) -> Option B

5. Quick Check:

   Use DATEDIFF('quarter', start, end) for full quarters [OK]

Hint: Use DATEDIFF with 'quarter' and correct date order [OK]

Common Mistakes:

• Dividing months by 3 instead of using 'quarter'
• Swapping start and end dates
• Using DATEADD instead of DATEDIFF for difference