Tableau Date Functions and How to Use Them for Your Analysis

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. 

Tableau Date 

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”)  
Tableau Date Functions
  • 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 
Date Functions Tableau

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   

Tableau DateAdd 

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) 
Datepart Values 
‘year’ Four-digit year 
‘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 
‘hour’ 0 -23 
‘minute’ 0 – 59 
‘second’ 0 -60 
  • 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). 

DateDiff 

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]) 
Datediff Tableau Date Functions

DateParse 

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”) Text Box4/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 
   

Today() 

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). 

Tableau Today Date Function

Now() 

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. 

Date Functions in Tableau

MakeDate & MakeDateTime 

Returns a date from a string of numbers, or a string dimension in your data. 

Makedate Tableau Date Functions

MakeDate(2016, 1, 31) 

MakeDate(str([Y1][M][D])) 

MakeDateTime(#2016,-1-1#, #13:00:00#) 

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!