Problem:
In your data, one or more of your fields contains a Date field but Tableau recognizes it as a String. This document explains how to fix this problem within Tableau.
This problem typically occurs when your date field name is concatenated like a string (i.e 03Jan2010).
Even when changing the data type, from string to Date, directly on the Dimensions window, only nulls are returned. So how do we fix it?
Here are a few examples of the Date Formats that Tableau recognizes:
- 23-Sep-10 (DD-MMM-YY)
- 1/11/2010 (MM/DD/YYYY)
- April 18, 2010 (Month DD, YYYY)
Step 1:
For this demonstration, we will parse through 03Jan2010 (DDMMMYYYY) format and reformat it into DD-MMM-YY, a format Tableau recognizes as a Date.
In Tableau Desktop, Click on Analysis -> Create Calculated Field
The important functions here are:
Left(string, num_chars): Returns the num_chars from start of string.
Mid(string, start, length): Returns characters from middle of string given a start position and length.
Right(string, num_chars): Returns the num_chars from end of string.
Step 2:
To get DDMMMYYYY into DD-MMM-YY, use:
Left([Your Date Field], 2) = DD
+
“-“
+
Mid([Your Date Field], 3, 3) = MMM
+
“-“
+
Right([Your Date Field], 2) = last 2 digits of YYYY
Finally, put Date() around that above formula.
It should look like this.
Step 3:
Hit OK
Now you'll have a new Date field in Date format ready for any time-related analysis.
