Convert from julian date from JD Edwards to regular

[jkorsten001]

Hi All,

I have trouble with the following. In my dataset i have julian date extracted from JD Edwards and I want to convert them to regular date (Gregorian).

My example is:

Julian date: 119003

Normal date: 03-01-2019 (day-month-year)

In Excel I had the formula: =DATE([Julian date]/1000;1;MOD([Julian date];1000)) 

But I cannot find one statement in Alteryx that gives me the right date format for my workflow. Can anyone help?

Thanks in advance!

[echuong1]

You should be able to use something like this in a Formula tool: DateTimeParse([Field1],"%Y%j")

See this post for more information:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Julian-Calendar-Conversion/td-p/482213...

[RolandSchubert · Accepted solution]

Hi @jkorsten001 ,

this is a specific type of Julian Date (first digit is century using 0 = 19, 1 = 20), digits 2 and 3 are years, last 3 digits represent day of year.

I've created a sample workflow and performed the operations in single steps to make it easier to reproduce, but of course you can use a single nested formula (or convert it to a macro).

Let me know if it works for you.

Best,

Roland

[jkorsten001 · Accepted solution]

Hi Ronald,

Thanks for your formula, if i apply your formula i am able to convert the julian date to 2 seperate columns:

DATE year: 2019

DATE days: 3

Now i need to convert these numbers back to a date: 03-01-2019 (3rd of january of 2019) 

Do you have any idea how to do this? 

Thanks! 

[atcodedog05]

Hi @jkorsten001 ,

Its there in @RolandSchubert 's workflow take a look.

Output:

Hope this helps 🙂

[jkorsten001]

@atcodedog05 and @RolandSchubert

Sorry i see it now. thx! 

[atcodedog05]

Happy to help 🙂

Cheers and happy analyzing 😀