Formula for converting to julian dating

All those LEN(), LEFT(), and MID() calls will be slooooww.Thinking about this from a math standpoint what we want to do is with the JDE/CYYDDD format is to remove the DDD (the right-most N digits) and extract what’s left.The starting way to get the left-most M digits from a number would be something like INT(LEFT(STR([number]), M)).

formula for converting to julian dating-46formula for converting to julian dating-30

The difference is that the YYYY portion of the calculation is returning the full four digit year so we subtract 1900 from that to get a number of years to add to the 1900-01-01 start date. We offer Tableau and Alteryx support, consulting, and training and can help you get the most out of your data!

Here’s a Tableau view showing the DDD and YYYY calculations along with the final Converted YYYYDDD calculation: Math is fun.

The way we can do that is in two steps: 1) turn the DDD portion into a decimal so CYYDDD becomes CYY.

DDD, then 2) truncate the DDD decimal portion so we’re just left with the CYY.

This is the number of years since 1900, so to generate the year we can use: Then to add the DDD day of year we can use DATEADD() with a starting date of the year date we just made. Here’s a link to the converting julian dates to dates workbook on Tableau Public.

Last modified 10-Apr-2020 16:27