Transforming Julian Dates into Tableau Date Fields with Modulo
Some date records are recorded in a format that Tableau does not recognize as a date field. Even wrapping them in a DATE() function does not convert the field into a usable date format. Two such examples of this are the JDE (JD Edwards) Julian date format and the modern Julian date format.
JDE Julian Date Format:
Modern Julian Date Format:
There are two ways to tackle this conversion. You can use string manipulation, using a slew of LEFT(), MID() and RIGHT() functions, or you can use math. The benefit of math over strings is a huge performance increase.
Quick disclaimer: my inspiration for the logic used here was based on something I read here:
The format of a JDE (JD Edwards) Julian date is CYYDDD. Essentially, “C” represents the century and starts at 0, which equates to 1900. If “C” = 1, the century equates to 2000, etc. The “YY” represents the 2-digit year that is added to the century. “DDD” represents the day of the year. For example, the 1st of February is the 32nd day in a year.
So an example of a JDE Julian date is 124032, which equates to the 1st of February 2024.
Keep in mind that if the century equals 0, it won’t appear at the beginning of the number. Hence, 24032 = 1 February 1924.
In Tableau, the method for converting JDE Julian dates to a usable date format requires that you first convert the field from a string to an integer.
From here on, you use the following calculation:
To simplify, the calculation separates the “DDD” or last 3 digits from the “CYY” by using modulo (%) 1000. Modulo gives you the balance left over after determining how many times one number can be divided by another, so in the case of JDE Julian date 124032, which we know equates to 1 February 2024, 124032 % 1000 leaves us with 32 days.
This is done in the section of code that looks like this:
[JDE Date] % 1000
We reverse engineer it somewhat to separate the “CYY” by dividing the full JDE Julian date by 1000, to split the number into a whole with decimals. The days themselves will come after the decimal point, so we can extract the “CYY” by wrapping it in an INT() function to get rid of the decimal places.
Now, you are left with the century number and the 2-digit years. So if we keep them as one whole number, such as “124” in our example above, then we can add these years to a starting date of the 1st January 1900.
We do this with the part of the code that looks like this:
DATEADD('year',INT([JDE Date]/1000),#1900-01-01#).So now we can take the days we extracted first and add them to the year we’ve formed above, as that year starts on the 1st.
If we were to do this, the code would look like this:
DATE(DATEADD('day',[JDE Date] % 1000,
DATEADD('year',INT([JDE Date]/1000),#1900-01-01#)))
However, because the year always begins on the 1st, we need to deduct a day when adding those days to the year, else we would have one more than we needed.
That’s why our code now looks like this:
DATE(DATEADD('day',[JDE Date] % 1000 - 1,
DATEADD('year',INT([JDE Date]/1000),#1900-01-01#)))
When it comes to modern Julian dates, the format is YYYYDDD. In this format, “YYYY” is the typical 4-digit year, and “DDD” is once again the day of the year. Hence, 2024032 equates to the 1st of February 2024. Likewise, 1923150 = 30 May 1923 and 1924150 = 31 May 1924, due to the extra day in February that results from the leap year.
Just as with the JDE Julian date, we need to make sure the field is converted to an integer. Beyond this, the process for converting these modern Julian dates into dates that Tableau can read is pretty similar to the one above.
We separate the days using the same modulo operation: [Modern Julian Date] % 1000
As for the year, it’s a little simpler. We already have the full year in this date format, not just the century number, so we no longer need to first add the extracted number to a starting date of January 1st, 1900. So we can simplify by creating a date that begins on the 1st, utilizing the MAKEDATE function:
MAKEDATE(INT([Modern Julian Date]/1000),1,1)
Then, we add the days to the year just as we did before, remembering to deduct a day as the year already starts on the 1st and not 0.
DATE(DATEADD('day',[Modern Julian Date] % 1000 - 1,
MAKEDATE(INT([Modern Julian Date]/1000),1,1)
))
And there you have it! Two types of Julian dates are converted into a usable format in Tableau quickly and easily—no strings attached.
