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?
Solved! Go to 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")
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!
@LordNeilLord very clean solution. Quick question, what does the "j" do at the end of the first formula?
@benakesh thank you! Exactly what I was looking for...
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