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


    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


    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



    Daddylonglegs,


    Your formulas are cool mate.


    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]


    daddylonglegs,


    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!