Julian Calendar Conversion

[mark22579]

I have a daily text file for our transaction and the date provided in Julian calendar format (see below). 

Date of Transaction = 10/23/2019

Date format sent in file = 2019296

I was hoping to use the DateTime tool but didn't see a way.  Someone suggested using a external file to lookup the date but that would mean I'd have to adjust it when there is a leap year (which 2020 is).

Is there a tool I can use?  Or do I need to build a formula?

[LordNeilLord · Accepted solution]

Hey @mark22579 

This formula will convert the date into a standard datetime:

DateTimeParse([Field1],"%Y%j")

Then you can change the format by adding on: DateTimeFormat(DateTimeParse([Field1],"%Y%j"), "%m/%d/%Y")

[JoeS · Accepted solution]

I think you will need to use a formula.

You can use this one:

DateTimeAdd(Left([Julian_Date], 4)+"-01-01",ToNumber(Substring([Julian_Date],4,99))-1,"days")

Edit: or use @LordNeilLord  more elegant solution!

[cking6178]

@LordNeilLord very clean solution. Quick question, what does the "j" do at the end of the first formula?

[benakesh]

@cking6178 

https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm

[cking6178]

@benakesh thank you! Exactly what I was looking for...

[asmit_kumar_pwc]

Hi All,

Try this:

DateTimeAdd("1899-12-30",[Julian Date],"days")

where [Julian Date] will be your column name in the data. You may adjust the date "1899-12-30" also to match with the output.

Thanks,

Asmit