# Convert Jd Edwards Julian Dates

• Is anyone here familiar with how to convert the JD Edwards Julian date format into standard Gregorian date, and can it be done using VBA?

Please note that the JD Edwards Julian date is different than the standard Julian Date and uses 6 digits instead of 7.

• Re: Jd Edwards Julian Date Conversion

Julian dates are normally 5 digits; what is the 6th digit used by Jd Edwards?

• Re: Jd Edwards Julian Date Conversion

Not sure about VBA solution. You can try the formula below it assumes you have values in cell A1:

=DATE(YEAR("01/01/"&TEXT(1900+INT(A2/1000),0)),MONTH("01/01/"&TEXT(1900+INT(A2/1000),0)),DAY("01/01/"&TEXT(1900+INT(A2/1000),0)))+MOD(A2,1000)-1

Biz

• Re: Jd Edwards Julian Date Conversion

With a formula you can just use:

=DATE(LEFT(A1,3),1,RIGHT(A1,3))

for VBA perhaps see solution(s) suggested here - I haven't tried them.....

or perhaps a simpler formula that would also cater for A1 being a formatted number rather than text, i.e. leading zeroes might not exist....

=DATE(A1/1000,1,MOD(A1,1000))

• Re: Jd Edwards Julian Date Conversion

Biz's formula seems to work, so I'm not sure why you'd need VBA, but here is what I came up with just in case you still need it. (P.S. I figured out what the 6th digit is.)

• Re: Jd Edwards Julian Date Conversion

Biz

• Re: Jd Edwards Julian Date Conversion

Quote from turtle44

Julian dates are normally 5 digits; what is the 6th digit used by Jd Edwards?

Not in my experience, all of the Julian dates I have come into contact with have been 6-7 digits long. The 6th digit in Julian Date represents the century value.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Both of your formulas work like a charm for the JD Edwards Julian Date: 105210 = 7/29/2005. Thank you for your help, this is much easier than the way I was trying to do the conversion!

• Re: Jd Edwards Julian Date Conversion

I really think we need to see some example data.

Boo!:yikes:

• Re: Jd Edwards Julian Date Conversion

THANK YOU!!! Saved my life today. I have spent many minutes trying to figure out how to do this.
dcurtis1