Extract Date from Integer in Tableau

Generally, the date field of the data we analyze will be either in string, integer, or date format. If it is in the string or integer format, you must extract the date with conversions or a built-in Tableau function. This Tableau article shows the steps to extract date from the integer column with a practical example.

To demonstrate this date extraction, we use a simple table report with Emp ID, Occupation, Hire Date, and the sales measure. If you observe the screenshot below, the Hire Date column displays the integers instead of the date because it is the data we got from the file.

A simple table report with numeric date field

How do you extract the date from the Integer in Tableau?

There are a couple of ways to achieve it, and on this page, we cover all of them with an example of each approach. We must write an expression to convert the integer to date; for this, we need a calculated field. So, click the Down arrow beside the HireDate measure, choose Create, and then select Calculated Field.

When you look at the date inside the HireDate column, the integer combines date fields ddMMYYYY. So, we have to use Tableau mathematical calculations like dividing the integer value by 1000000 and the percentage of 1000000 to extract the date. For instance, divide by 1000000 to extract the first two digits (in this scenario, get the day). 

The expression below extracts the day from the integer date. Here, INT() converts the result to an integer.

INT([HireDate] / 1000000)
Create a Tableau Calculated Field to extract day from the Integer column

Let me add that Day calculated field to the Rows shelf of the table report by double-clicking on it. Looking at the output, you can see the day numbers, i.e., 28, 29, .., 1. For the remaining charts in Tableau, please click here.

Create one more calculated field for the month and write the expression below to get the month numbers.

INT(([HireDate] % 1000000) / 10000)
Create a Tableau Calculated Field to extract Month from the Integer column

Add the Month field to the table to see the result and create a new calculated field to write the Year expression. The code below prints the integer’s last four digits, i.e., Year.

INT(([HireDate]% 10000))

As the Year field is the integer, by default, it shows the thousand separator (comma). So, right-click any value of the Year Column and select the Format Option. Next, change the Numbers option from Automatic to Number(Custom) under the Default section and uncheck the Include Thousand separator. 

Create a Tableau Calculated Field to extract Year from the Integer column

Let me add all the extracted Tableau calculated Date fields, Day, Month, and Year, to the table to compare them against the HireDate integer column.

Extract Day, Year, Month, and Date from Integer column in Tableau

Tableau DATEPARSE to Extract Date from Integer

In Tableau, a built-in date function called DATEPARSE helps to convert or parse the string to the date format. However, it will only work or extract for the string fields, so you must convert the integer date to the string format to use this Tableau DATEPARSE function.

Let me create one more calculated field called NewDate and use the STR() function to convert integer date to string and DATEPARSE to convert the string to datetime. Remember, the date_part (dMMyyyy) has to match the HireDate data. For more functions >> Click Here.

DATEPARSE('dMMyyyy', STR([HireDate]))
Tableau DATEPARSE to Extract Date from Integer

Next, add that field to the table report. By default, when you add the newly created NewDate field, it returns the Date and Time. So, please use the Format menu to change it to date. 

Once you get the complete date, you can use the following Tableau functions to extract the date fields from the integer.

  1. The DAY() function helps extract the day number. 
  2. MONTH() function is used to extract the month number.
  3. Year() function helps extract the Year.

Please edit the existing calculated fields often Day, Month, and Year to the DAY(NewDate), MONTH(NewDate), and Year(NewDate)

By default, when you add the Day field to the Rows Shelf, it chooses the Continuous. So, it would be best to change it to Discrete to see the actual value. Otherwise, change them to Dimensions. Do the same for the Month and Year fields.

Tableau DATEPARSE, DAY MONTH, and YEAR functions to Extract Date from Integer column

Instead of converting the NewDate to the continuous or exact date, you have multiple options if you drag it to the Rows shelf. By clicking the down arrow, you can extract a day, month, year, weekday, etc, from the date field.

Tableau DATEPARSE, DAY MONTH, and YEAR functions to Extract Date from Integer column result

Do the same for the HireDate as well.