Convert from julian date from JD Edwards to regular

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!

Date Time

Accepted answers

RolandSchubert

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

JulianDate.yxmd

jkorsten001

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!

All comments

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

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).