When it comes to Date Calculations in Tableau, your go-to solution in Tableau will be the Tableau Date Functions. It is part of the amazing things you can do with the Tableau calculated field. Today, we will discuss the most common Tableau Date Functions which we know will be highly useful in your data analyses.
Tableau Date Calculations
Date calculations will likely be one of the most common calculations you use. Therefore, it is imperative you understand the most common Tableau Date Functions because they will likely become very useful to your analysis. Following is a list of different types of date calcs and when and how to use them.
Converts an input – usually a string value – into an actual date that you can then use as dates in your views or in date calculations.
- Date(“January 1, 2016”)
- This will convert your date to something like this: 1/1/2016
- Once you click OK you will see the calculated field on the left side of your screen under Dimensions or Measures as appropriate. You can always change it if you need it to be the other.
- You can format this to show however you want by clicking the down arrow on the calculated field and choosing Default Properties > Date Format
The date in this calculation we just created doesn’t have to be a hardcoded value (i.e. Jan 1, 2016). This can be another field that looks like a date but is actually a string. E.g.[Year] (type: string or text value). The example below shows how to convert strings or text into dates.
- Date(str([Year]) + ‘/’ + str([Month]) + ‘/’ + str([Day]))
- This converts three separate text fields into one actual date, E.g. 1/1/2016
Adds a specified time period to a date. You specify the date part you want to add and this calc adds it to the date you’ve selected. This is useful when you need to calculate new dates, set time thresholds, create reference lines, or create new dimensions.
- DateAdd(Datepart, Increment, Date) => DateAdd(‘month’,3,[Order Date])
- In the example above, we’ve added 3 months to the original Order Date.
- The datepart portion is always in lower case and in single quotes. The table below shows allowable values)
|‘quarter’||1 – 4|
|‘month’||1 – 12 or “January”, “February”, etc.|
|‘dayofyear’||Day of the year; Jan 1 is 1, Feb 1 is 32, Dec 31 is 365|
|‘day’||1 – 31|
|‘weekday’||1 -7 or “Sunday”, “Monday”, etc.|
|‘week’||1 – 52|
|‘minute’||0 – 59|
- DateAdd(‘day’, -30, [Today])
- This subtracts 30 days from the current date of Today. This is useful when you want to show a rolling window of time (say past 30 days).
Calculates the time difference between two dates at the level that you specify, such as month, day, hour, etc.
[End Date] – [Start Date] will give you the exact amount of time between the two dates, which sometimes you want. For example, [1/2/2016 8:30 pm] – [1/1/2016 2:45 pm] = -24.79. This is the exact difference in hours and minutes.
This is essentially what we did right on the view on Day 2 when we calculated the number of days between Order Date and Ship Date.
Sometimes, you want it at a specified granularity and don’t need as much exactness. Hence the DateDiff function.
- DateDiff(Datepart, Start Date, End Date)
- Datepart specifies the date granularity you want. See the preceding table for acceptable inputs.
- Note that the start date occurs before the end date in this calc, so be sure that whatever date you are subtracting from is placed last.
- DateDiff(‘day’, [Order Date], [Ship Date])
Converts a string to a specified date format.
- DateParse(format, string)
- DateParse(“dd.MMMM.yyyy”, “January 1, 2016”)
See table below for acceptable formats:
|Date string||DATEPARSE function||Display value returned|
|16.8.97||DATEPARSE (“d.M.yy”, “16.8.97”)||8/16/1997 12:00:00 AM|
|7/12/91||DATEPARSE (“d/M/yy”, “7/12/91”)||12/7/1991 12:00:00 AM|
|12Sep2012:9:8:7.6546||DATEPARSE (“ddMMMyyyy:H:m:s.SSSSSS”, “12Sep2012:9:8:8.6546”)||9/12/2012 9:08:09 AM |
|5:09||DATEPARSE(“h:m”, “5:09”)||5:09:00 AM|
|97.11||DATEPARSE(“yy.MM”, “97.11”)||11/1/1997 12:00:00 AM|
|9743||DATEPARSE(“yyMd”, “9743”)||4/3/1997 12:00:00 AM|
|97-8-14 3:2:1||DATEPARSE(“yy-MM-dd h:m:s”, “97-8-14 3:2:1”)||8/14/1997 3:02:01 AM|
|19977345||DATEPARSE (“yyyyMdHm”, 19977345”)||7/3/1997 4:05:00 AM |
Returns the current date. This is useful for creating reference lines showing relationships in your data to today (e.g. your deliverables deadlines in relation to today), as well as calculations based on today’s date (such as rolling windows).
Returns the current date and time. Same as above, but adds the hours, minutes and seconds. Useful if you need more granular information or are working with intra-day data or data that updates in near real-time throughout the day.
MakeDate & MakeDateTime
Returns a date from a string of numbers, or a string dimension in your data.
MakeDate(2016, 1, 31)
These are just some of the most useful Tableau Date Functions you can use in your data analysis. But if you want to learn more, be sure to try our individual and corporate Tableau classes today!