Converting Julian Dates to Gregorian Dates Year Month Day in Excel and SQL Language

Dates format as configured in JDEdwards XE, JDEdwards 8.10  8.12 EnterpriseOne

MS Excel,  tested on Excel 2003 and Excel 2007, lap year OK

Convert Cell A2 to Gregorian date:

=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

Convert Cell A2 to Gregorian date:

=DATE(1900+INT(A2/1000),1,MOD(A2,1000))

Formula 3:

Convert Cell A2 to Gregorian date:

=DATEVALUE(CONCATENATE("01/01/",1900+INT(A2/1000)))+INT(RIGHT(A2,3)-1)

xcel julian date convert

SQL to convert julian dates to Gregorian dates

Sample converting history sales order SDIVD, invoice dateTested on Oracle 8, Oracle 10. SELECT F42119.SDDOCO as sales_order, 19 + substr( F42119.SDIVD, 1, 1 ) || substr(F42119.SDIVD, 2,2) || '-' || to_char( to_date( substr( F42119.SDIVD, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDIVD, 4, 3 ) - 1 , 'DDD') , 'DD' ) AS invoice_date from proddta.f42119;

JDEdwards Julian Date Convert