Date calculations (DATEDIFF, DATEADD) in Tableau - Cell-by-Cell Formula Trace

Sample Data

This data shows order dates and corresponding ship dates for three orders.

Cell	Value
A1	Order Date
A2	2024-01-01
A3	2024-01-15
A4	2024-02-01
B1	Ship Date
B2	2024-01-05
B3	2024-01-20
B4	2024-02-10

Formula Trace

DATEDIFF('day', [Order Date], [Ship Date]) + DATEADD('day', 5, [Order Date])

Step 1: DATEDIFF('day', '2024-01-01', '2024-01-05')

Step 2: DATEADD('day', 5, '2024-01-01')

Step 3: 4 + '2024-01-06'

Cell Reference Map

     A           B
1  Order Date  Ship Date
2  2024-01-01  2024-01-05
3  2024-01-15  2024-01-20
4  2024-02-01  2024-02-10

Formula references A2 and B2 for the first row calculation.

The formula uses Order Date (A2) and Ship Date (B2) to calculate the difference and add days.

Result

     A           B           C
1  Order Date  Ship Date   Result
2  2024-01-01  2024-01-05  Error
3  2024-01-15  2024-01-20  
4  2024-02-01  2024-02-10  

Column C shows the formula result for row 2, which is an error due to type mismatch.

The result column shows an error because the formula tries to add a number to a date, which is not allowed.

Sheet Trace Quiz - 3 Questions

What does DATEDIFF('day', '2024-01-01', '2024-01-05') return?

A 5

B 6

C 4

D 1

Key Result

DATEDIFF calculates the difference between two dates as a number; DATEADD shifts a date by a number of units; adding them directly causes a type error.

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