Julian Calendar Conversion

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?

Preparation

Accepted answers

LordNeilLord

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

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!

All comments

LordNeilLord

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

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?

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703