You have two dates in your data, an order date and a ship date or a start date and an end date, and you need to know how far apart they are. In Tableau the function for this is DATEDIFF. This guide covers the syntax, the date parts you can use, the optional start-of-week argument that trips people up, and a full worked example with Sample – Superstore.
DATEDIFF syntax
According to Tableau’s date functions reference, the syntax is:
DATEDIFF(date_part, date1, date2, [start_of_week])- date_part: a string such as
'day','week','month','quarter'or'year'. This is the unit the result is counted in. - date1: the earlier (starting) date.
- date2: the later (ending) date.
- [start_of_week]: optional. Something like
'sunday'or'monday'. It only matters when you count weeks. If you leave it out, the data source’s start of week setting is used.
The function returns an integer: the difference between date1 and date2 expressed in units of the date part. Tableau’s own example:
DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751The # signs are date literals. They tell Tableau that the value between them is a date, the same way quotes mark a string. Without them, 3/25/1986 would be read as a division.
Valid date_part values
The date parts you can pass to DATEDIFF are:
'year','quarter','month','week','day''dayofyear','weekday''hour','minute','second'(useful with datetime fields)'iso-year','iso-quarter','iso-week','iso-weekday'(ISO 8601, where weeks always start on Monday)
The date part goes in single or double quotes and is not case sensitive in practice, but lowercase in quotes is what you’ll see in Tableau’s documentation, so stick with that.
Worked example: days to ship in Sample – Superstore
Superstore has both Order Date and Ship Date on every row, which makes it perfect for this.
Step 1: Create the calculated field
Go to Analysis > Create Calculated Field, name it Days to Ship, and enter:
DATEDIFF('day', [Order Date], [Ship Date])Click OK. Because the result is a number, Tableau places the field with the measures in the Data pane.
Step 2: Build the view
- Drag Ship Mode to Rows.
- Drag Days to Ship to Columns.
- Click the Days to Ship pill, go to Measure (Sum) and change it to Average.
Summing days across thousands of rows gives you a meaningless total. Averaging gives you the typical shipping time per ship mode, which is the question you actually want to answer. You should see Same Day sitting near zero and Standard Class taking the longest.
Step 3: Flag late shipments
Once you have a row-level number, you can feed it into other logic. For example, to flag orders that took more than five days:
IF DATEDIFF('day', [Order Date], [Ship Date]) > 5 THEN "Late"
ELSE "On time"
ENDDrop that on Color and you have a quick shipping-performance view. If you are new to this structure, see our guide on how to write an IF statement in Tableau.
How DATEDIFF counts: boundaries, not elapsed time
This is the part that surprises people. DATEDIFF counts how many date part boundaries are crossed between the two dates. It does not measure elapsed time and round it.
Tableau’s help shows this clearly with weeks. September 22, 2013 was a Sunday and September 24, 2013 a Tuesday:
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'monday') = 1
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'sunday') = 0With weeks starting Monday, the two dates fall in different weeks, so the answer is 1. With weeks starting Sunday, they’re in the same week, so the answer is 0. Same two dates, two days apart, different results.
The same logic applies to months and years. December 31 to January 1 is one day apart, but it crosses a year boundary, so a 'year' DATEDIFF returns 1. That means DATEDIFF with 'year' is not a reliable way to calculate someone’s exact age or tenure. If you need precision, calculate in days and divide, or compare month and day parts explicitly.
Common variations
Days from a date until today
DATEDIFF('day', [Order Date], TODAY())TODAY() returns the current date. Use NOW() instead if you need a datetime, for example when counting hours. Note that with a live connection, the system date can come from a server in another time zone.
Days since a customer’s first order
Combine DATEDIFF with a FIXED level of detail expression to anchor every row to the customer’s first purchase:
DATEDIFF('day',
{FIXED [Customer Name] : MIN([Order Date])},
[Order Date])That’s a classic cohort-analysis building block. We walk through the LOD part in how to use FIXED LOD expressions in Tableau.
Bucketing the result
If you want labels like “0-2 days”, “3-5 days” and “6+ days”, wrap the DATEDIFF in an IF statement with ELSEIF branches. CASE won’t work for ranges, because CASE only matches exact values (see the CASE statement guide for when CASE is the better choice).
Looking at the distribution
Averages hide outliers. Put Days to Ship on a box plot by Ship Mode to see the spread, or check the median instead of the mean.
Common mistakes and FAQ
Why is my DATEDIFF result negative?
The order of the dates is reversed. DATEDIFF subtracts date1 from date2, so put the earlier date first. If date2 comes before date1, you get a negative number.
Why does DATEDIFF return null?
If either date is null, the result is null. In Superstore that won’t happen, but in real data an unshipped order often has an empty ship date. Filter those rows out or handle them with IFNULL, for example IFNULL([Ship Date], TODAY()) if you want to measure open orders up to today.
DATEDIFF says I passed a string, not a date
Your field is stored as text. Convert it first with DATE([Field]), or with DATEPARSE if the format isn’t recognized automatically, for example DATEPARSE('yyyy-MM-dd', [Field]). Then use the converted field in DATEDIFF.
What’s the difference between DATEDIFF and subtracting dates?
Subtracting two dates directly ([Ship Date] - [Order Date]) returns the number of days, and with datetime fields it can return a decimal. DATEDIFF lets you choose the unit and always returns an integer. Use DATEDIFF when you want weeks, months or years, or when you want the result to be explicit to anyone reading the calculation.
Practice it for the exam
Date calculations show up regularly on Tableau certification exams, often as “what does this formula return?” questions where the week-start or boundary behavior is the trap. Test yourself with our Desktop Specialist practice questions, and if you’re going for the next level, the Data Analyst study guide covers calculations in more depth.