Convert Jd Edwards Julian Dates

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.....

http://www.systeminetwork.com/…ms/showthread.php?t=43630

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.)

Public Function ConvertJulian(JulianDate As String) As Date
   Dim lDays As Long
   Dim lYear As Long


   lDays = CLng(Right(JulianDate, 3))
''''''
'   If lDays > 366 Then
'      'is this an error
'   End If
'''''
   If Len(JulianDate) < 4 Then
      lYear = 1900
   Else
      lYear = 1900 + CLng(Left(JulianDate, Len(JulianDate) - 3))
   End If
   If lYear = 1900 Then lDays = lDays - 1
   ConvertJulian = DateSerial(lYear, 1, lDays)
End Function

Re: Jd Edwards Julian Date Conversion

Quote from daddylonglegs

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.....

http://www.systeminetwork.com/…ms/showthread.php?t=43630

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))

Display More

Daddylonglegs,

Your formulas are cool mate.

Biz

Re: Jd Edwards Julian Date Conversion

I really think we need to see some example data.

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