Julian Date to Short Date

Jeff_Berman

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)

Mike TV

@Jeff_Berman

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.

Jeff_Berman

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

https://www.extendoffice.com/documents/excel/2474-excel-convert-julian-date-to-calendar-date.html

Paul Newcome

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?