Julian Date Conversion in Access

I know this has probably been talked about before but I can’t find anything in the archives.

Does anyone know a formula that I can use in Access or VB Code to convert a date from mm/dd/yy to Julian? And the other way around?

Various reposts follow: (yeah, I saved 'em)
=====================
Kenny,
I use this formula in MS Access to convert the JDE Julian to a date value
where IREFFF is the field in JDE that has the JDE Julian, (in this instance
this is the Effective From date in the F3003 file.)

DateValue("12/31/" & (Int([IrEFFF]/1000))+1899)+([irefff]-Int
([irefff]/1000)*1000)

Here's how it would look in MS Excel

=DATEVALUE("12/31/" & (INT(B1/1000))+1899)+(B1-INT(B1/1000)*1000)
where cell B1 has the JDE Jullian Date.

Good Luck!
Hal McGee
=============================
I am reposting this from Nigel [[email protected]]: (and of course, you can
always run DW P00365 which will populate file F00365 which will have julian
dates and the conversion in many formats)


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

==========================
FOR QRY400:

Whoops, I was playing with Leanne's formula and left my test data in it.
Here goes again:

date(substr(digits(A5***+1900000),2,7))

where A5*** is the julien fieldyour are trying to convert.

============================

Here are some old posts I saved that may be of assistance.

The first was from Nigel [[email protected]], the second from Hal_McGee
[[email protected]]

****************************************************************************
****************

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
--------------------------
Visit the forum to view this thread at:
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=W&Number=27
154
+ - - - - - - - - - - - - - - - - - - - - - - - -+
This is the JDEList World(tm) mailing list / forum.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found on the JDEList Forum at
http://www.JDEList.com

JDEList is not affiliated with JDEdwards®

+ - - - - - - - - - - - - - - - - - - - - - - - -+
****************************************************************************
**************


Kenny,
I use this formula in MS Access to convert the JDE Julian to a date value
where IREFFF is the field in JDE that has the JDE Julian, (in this instance
this is the Effective From date in the F3003 file.)

DateValue("12/31/" & (Int([IrEFFF]/1000))+1899)+([irefff]-Int
([irefff]/1000)*1000)

Here's how it would look in MS Excel

=DATEVALUE("12/31/" & (INT(B1/1000))+1899)+(B1-INT(B1/1000)*1000)
where cell B1 has the JDE Jullian Date.

Good Luck!
Hal McGee




--------------------------
To view this thread, visit the JDEList forum at:
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=W&Number=2
7137
+ - - - - - - - - - - - - - - - - - - - - - - - -+
This is the JDEList World(tm) mailing list / forum.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found on the JDEList Forum at
http://www.JDEList.com

JDEList is not affiliated with JDEdwards®

+ - - - - - - - - - - - - - - - - - - - - - - - -+

****************************************************************************
*************

Those old posts were very helpful for going from Julian to MM/DD/YY.

What I really need though it to go from MM/DD/YY to Julian.

I was hoping not to have to set up a link to F00365.

Getting the Day number of the Year is the part that to me gets a little tricky. I can not seem to find any MS function that does only that. Best I can find is DateDiff.

DayNumber = DateDiff("d", [FistDayofShipDateYear], [ShippedDate])

Concatenate 1 + 2digit year + DayNumber

Of course its not as simple as that because DayNumber would contain something like 5 and not 005.

Anyone think I am way off base here?

Paste this function into Excel in the same way as my earlier posts:

Function DateToJulian(NormalDate As Date) As Long 'convert normal date to julian date.
YearNum = Year(NormalDate) - 1900 'get julian year by subtracting 1900
DaysNum = DateSerial(Year(NormalDate), Month(NormalDate), Day(NormalDate)) - DateSerial(Year(NormalDate), 1, 1) 'get the day number in the year by subtracting the serial number of Jan 1st of that year
DateToJulian = YearNum & "," & Format(DaysNum + 1, "00#") 'concatenate the julian date format to ensure 3 digits
DateToJulian = CLng(DateToJulian)
End Function

Scott,
I have been very busy or I would have answered sooner. I am assuming
that you have a date in either Access or Excel in date format and not in a
text format. If this is true, the formula shown below that I created in
Excel takes a date in cell C21 and returns the JDE Julian:

=(YEAR(C21)-1900)*1000+C21-DATEVALUE("1/1/"&YEAR(C21))+1

I take the year - 1900 to get the two or three digit value JDE is looking
for and multiply by 1000 to push the value to the left in the string. This
takes care of the 0 problem you pointed out in another post. Next I figure
out the Julian day for that date within the year by subtracting the date
minus the datevalue for Jan 1st of the same year and then correcting by
adding 1 back since Jan 1st has a Julian value of 1. Add this value to the
year value and you get the complete JDE Julian.

Hope this helps.
Good Luck
Hal

Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC

Thanks that was exactly what I was looking for.