KNOWLEDGE BASE

## Converting JDE Julian Date Fields to a Supported Date Format

Published: 10 Jul 2014

### Question

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

### Environment

• Tableau Desktop
• JDE Julian Date Field

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.