Converting JDE Julian Date to DD/MM/YYYY Format In Excel
Place your JDE Julian Dates in Column A
Suppose your first date is in Cell A1 and let's say that it is 113338
Now, in cell B1 (or wherever you want the desired output), paste the following formula :
=DATE(YEAR("01/01/"&TEXT(1900+INT(A1/1000),0)),MONTH("01/01/"&TEXT(1900+INT(A1/1000),0)),DAY("01/01/"&TEXT(1900+INT(A1/1000),0)))+MOD(A1,1000)-1
Copy it here
Do NOT forget to replace the cell address, incase your cell is different than A1
If there are multiple dates in the column, just drag the cell to fill the column corresondingly
Please note that by default the dates will be in MM/DD/YYYY format.
In this example, the result obtained was : 12/4/2013
If you want the output in DD/MM/YYYY format, right click on the cell > Format Cells... > Number (Tab) > Date
Select Location as "English (United Kingdom)" and double click the appropriate "Type"
Converting DD/MM/YYYY Format to JDE Julian Date In Excel
Convert your date from DD/MM/YYYY format to MM/DD/YYYY format
Place your Dates (any format, but it must be parsed as a date) in Column A
Suppose your first date is in Cell B1 and let's say that it is 12/04/2013 (4th Dec 2013)
Now, in cell C1 (or wherever you want the desired output), paste the following formula :
=(YEAR(B1)-2000+100)*1000+B1-DATE(YEAR(B1),"01","01")+1
Copy it here
Do NOT forget to replace the cell address, incase your cell is different than B1
If there are multiple dates in the column, just drag the cell to fill the column corresondingly