Date calculations (DATEDIFF, DATEADD) in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate days difference between two dates

Given two dates, Order Date as 2024-01-10 and Ship Date as 2024-01-15, what is the result of the following Tableau calculation?

DATEDIFF('day', [Order Date], [Ship Date])

A 5

B 6

C 4

D Error: Invalid function usage

💡 Hint

DATEDIFF counts the number of day boundaries crossed from the first date to the second.

The difference between 2024-01-10 and 2024-01-15 is 5 days because it counts the days between the two dates, excluding the start date.

❓ dax_lod_result

intermediate

Add months to a date using DATEADD

What is the result of this Tableau calculation if Order Date is 2024-03-31?

DATEADD('month', 1, [Order Date])

A Error: Invalid date

B 2024-04-31

C 2024-05-01

D 2024-04-30

💡 Hint

Adding one month to March 31 adjusts to the last valid day of April.

Since April has only 30 days, adding one month to March 31 results in April 30.

❓ visualization

advanced

Best visualization for monthly sales growth

You want to show the month-over-month sales growth percentage for the last 12 months. Which visualization type is best to clearly show this trend?

A Bar chart with total sales per month

B Pie chart showing sales distribution by month

C Line chart with months on the x-axis and growth % on the y-axis

D Scatter plot with sales vs. months

💡 Hint

Trends over time are best shown with continuous lines.

A line chart clearly shows how sales growth changes month to month, making trends easy to spot.

❓ dax_lod_result

advanced

Calculate quarter difference between two dates

You have Start Date as 2023-11-15 and End Date as 2024-05-10. Using Tableau's DATEDIFF function, what is the quarter difference?

DATEDIFF('quarter', [Start Date], [End Date])

A 2

B 3

C 1

D 4

💡 Hint

Count how many quarter boundaries are crossed from start to end date.

From Q4 2023 (2023-11-15) to Q2 2024 (2024-05-10) crosses 2 quarter boundaries: January 1, 2024 (start of Q1) and April 1, 2024 (start of Q2).

🔧 Formula Fix

expert

Identify error in date calculation formula

Which option will cause an error when calculating the difference in days between two dates in Tableau?

DATEDIFF('day', [Date1], [Date2])

Assume Date1 and Date2 are fields in the data.

A [Date1] and [Date2] are both valid date fields with no nulls

B [Date1] is a string field with date text, [Date2] is a date field

C [Date1] and [Date2] are both date fields but some values are null

D [Date1] is a date field, [Date2] is a datetime field

💡 Hint

DATEDIFF requires both inputs to be date or datetime types.

If one field is a string, Tableau cannot perform date calculations and will raise an 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