Julian Date to Short Date
I am trying to write a formula to convert a Julian date to a short date. This formula isn't getting the job done. Any recommendations would greatly be appreciated.
*note  this formula does work in excel
Julie Date Column:
21123
Short Date Column (text/number type and date type were tried)
=IF(0 + (LEFT([Process Julian Date]@row, 2)) < 30, 2000, 1900) + LEFT([Process Julian Date]@row, 2), 1, RIGHT([Process Julian Date]@row, 3)
Answers

The part that's broken in your formula is the 1 before the final RIGHT([Process Julian Date]@row, 3). What's that supposed to be doing? I've been trying to Google Julian Date and wrap my brain around how you'd calculate the date on paper to see how the SmartSheet formula should be written. I'm not there yet.

@Mike TV Thanks! Here is where I found the formula. Can't say about the 1?

I am finding quite a bit on 6 digit julian, but documentation on 5 digit julian is pretty sparse.
From what I have read it is
ccddd
where "cc" determines the century and "ddd" is how many days within that century.
I have seen less than 20 and less than 30 to determine the century, so I will stick with what you are currently using (less than 30). Once we establish the century, we would take 1 January of that century and add the number of days to it listed in the last three digits.
=DATE(IF(VALUE(LEFT([Process Julian Date]@row, 2)) < 30, 2000, 1900), 1, 1) + VALUE(RIGHT([Process Julian Date]@row, 3))
Does that work?
Help Article Resources
Categories
Check out the Formula Handbook template!