Converting JDE Julian Dates into something more readable .

Has any one developed a formula for converting the JDEdwards Julian
Dates to something more readable. I need the data for an excel spread
sheet.

Michael Kenney
IT Services
University of Windsor

The following VB code will give you a function that you can use the same as the one's already built into Excel. Open the VB Editor with Alt+F11 and add a new module (on the Insert menu). Paste in the code, and then close the editor. To use it, if you have a Julian date in cell A1 then in any other cell enter the formula "=ConvertJulian(A1)" without the quotes. This will give you an Excel date serial number. Simply format the cell to whatever date format you wish. Also note that you will find the new function in the Function Wizard list under Custom or possibly Date.

Copy & Paste this:-

Option Explicit

Function ConvertJulian(JulianDate)
'convert JDE style Julian dates to Excel date serial number.
'Format the cell as date.
Dim YearNo As Integer, DaysNo As Integer, DaysInYear As Integer

YearNo = Left(JulianDate, Len(JulianDate) - 3) + 1900
DaysNo = Right(JulianDate, 3)

If YearNo Mod 4 = 0 Then
DaysInYear = 366
Else
DaysInYear = 365
End If

If DaysNo <= DaysInYear And DaysNo >= 1 Then
ConvertJulian = DateSerial(YearNo, 1, 1) + DaysNo - 1
Else: ConvertJulian = "Invalid Julian Date"
End If
End Function

World A7.3 cum 6

Thanks for the post. I'm sure it will come in handy in the future.

Paul Thompson
IT Business Analyst
[email protected]
7.3 cum 7

You can run the DREAM Writer P00365. It writes one record into the F00365 file for every date, in different formats.

P00365 Dream writer does the trick for converting dates. Is there a way to auto submit the job using IBM's WRKJOBSCDE Job Schedule options?

you can use the following formula in excel to convert the date after you have downloaded it into excel'
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))