KNOWLEDGE BASE

Converting JDE Julian Date Fields to a Supported Date Format


Published: 10 Jul 2014
Last Modified Date: 21 Oct 2016

Question

How to convert JDE Julian Dates to a supported date format in Tableau Desktop.

Environment

  • Tableau Desktop
  • JDE Julian Date Field

Answer

The following instructions can be reviewed in the workbook attached below.

Create a calculated field to determine Julian years

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box:
    • Name the calculated field. In the example workbook, the calculated field is named "Julian Year".
    • In the formula field, create a calculated field similar to the following:
      IF LEN(Julian JDE Date) = 6 AND LEFT([Julian JDE Date], 1) = '1' THEN '20' + MID([Julian JDE Date], 2, 2) ELSEIF LEN([Julian JDE Date]) = 5 THEN '19' + MID([Julian JDE Date], 1, 2) END
  3. Click OK.

Create a date calculated field that adds the number of days from the JDE Julian Date to the Julian Year field

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box:
    • Name the calculated field. In the example workbook, the calculated field is named "Julian Date - Tableau".
    • In the formula field, create a calculated field similar to the following:
      DATE(DATEADD('day', INT(RIGHT([Julian JDE Date], 3)), DATE('01/00/'+[Julian Year])))
  3. Click OK.

Additional Information

The ability to automatically convert JDE Julian Dates to a supported date format is functionality that is not currently built into the product.
Did this article resolve the issue?