I recently had to convert some date fields formatted as Julian dates into more usable Tableau date data types (it’s not a date format that Tableau natively understands) and my web search got me a little frustrated because all the solutions I found were long formulas using string manipulation functions like LEFT(), RIGHT(), etc.
That kind of solution can get the job done, but there’s something to be left desired: String manipulation is sloooooooooooooooooooooooooooooow How about one thousand times slower (or more) than using math functions!
I like my calculations to be efficient from the beginning: The practical reason is that I don’t have to spend time later going back to do performance tuning.
Computers were built to do math really really fast so whenever possible I’ll use math to do my date conversions.
The aesthetic reason is that there’s an elegance to creating equations that use the least number of steps, and the play reason is because math is fun!
All we need is a starting date, and in this case we can take advantage of the CYY structure to figure out the appropriate starting date.
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.
"Julian date format" refers to a format where the year value of a date is combined with the "ordinal day for that year" (i.e. For example, for the date January 21, 2017, you might see: This formula builds the final result in 2 parts, joined by concatenation with the ampersand (&) operator.
A date in this format may include a 4-digit year (yyyy) or a two-digit year (yy) and the day number may or may not be padded with zeros to always use 3 digits.Then the INT() around that truncates the decimal places and leaves us with the remaining whole number.