Julian to Gregorian date conversion in EXCEL

I found a thread stating that the formula for converting Julian ot Gregorian is the following;
date(substr(digits(xxxxxx+1900000),2,7)) Note: xxxxxx =3D3D date field name.

I'm not sure what 3D3D date field name means, but I've entered this formula where xxxxxxx equals the cell containing the julian date and I'm getting an error stating there's too few arguments for this function. I'm using Excel version 2003 professional edition.

Can someone help me understand what I'm doing wrong?
Thanks.

that is an SQL formula which will execute quite nicely on the iseries; you populate the xxxxxx with the name of the file field which actually contains the date data.

The DATE function in excel expects this format
=DATE(A2,B2,C2)

The SQL statement is not meeting that criteria even when you replace xxxxxx with a cell reference.
date(substr(digits(xxxxxx+1900000),2,7))
is basically date(yyy) - a single argument not the three that excel expects. in the SQL statement, yyy boils down to: convert the result of xxxxxx +1900000 to characters and take 7 of those characters starting in position 2. SQL expects one argument for its date function.

read the 3rd entry in that same thread and you should see instructions specific for excel (it involves FLOOR, MOD and then DATE). I am assuming you are referring to the thread at
http://www.jdelist.com/ubb/showflat.php?Cat=0&Number=86248

i don't believe excel even has a DIGITS function.

looking closely, there are some typos in that original thread which detailed the excel formula's individual steps.

i corrected the typos and combined them here into a one step formula:

= DATE(FLOOR(xxx/1000+1899,1), 12, 31) + MOD(xxx, 1000)

where xxx = the cell with the julian date.

and, of course, be sure to format the gregorian cell to be a date format. if you don't format the gregorian conversion cell to be a date format you will just get the numeric result of the calculation instead of the nice date layout you want.

I use the following formulas to convert dates in MS Excel:

If the julian date is in cell A2 then in cell B2 put
'=DATE(INT(A2/1000)+1900,MONTH(+A2-INT(A2/1000)*1000),DAY(+A2-INT(A2/1000)*1000))

If the MS date is in cell C2, then the julien date is:
'=(YEAR(C2)-1900)*1000+C2-DATE(YEAR(C2),1,1)+1

These formulaes are Y2K compliant.

Any SQL magic for Gregorian (specifically today's date) converted to the JDE Julian CYYDDD?

get julian date from f00365. use curdate() in SQL to get system date and field ONDATE in f00365 has the gregorian date.
example:
select * from myfile where
juliandatefield = (select ondtej from f00365
where ondate=curdate())

or were you looking for an actual calculation that doesn't rely on f00365 being maintained?

I found a function from one of my other email lists a couple of years ago.
I'll try to cut and paste the info here.



Convert J.D. Edwards Dates to Other Formats



Hey, Howard:

I want to convert a J.D. Edwards date, (CYYDDD where C represents the
century, YY represents the year, and DDD represents the day of the year) to
a more readable format, (like YYYY-MM-DD). I can do this in Crystal Reports,
but is there a way to do it in SQL?




-- Domenic

This is a good one, Domenic, as I first thought it was impossible, but on
further reading of the IBM <http://www.ibm.com> documentation, I found a
little known use for the DATE function. I found that DATE can take a variety
of arguments. As I was reading the documentation on DATE, I noticed that one
of the acceptable date formats is a seven-character string in the form
YYYYNNN where YYYY is the year and NNN is the day number. When passed this
information, DATE will return a date data type value representative of the
string that was passed in.

To take advantage of this feature, we need only to convert your DECIMAL(6,0)
representation of the date into the required CHAR(7) representation and pass
it to the date function. Let's do it!

First we create some test data:

CREATE TABLE mceis.t1 (d DECIMAL(6,0))
INSERT INTO mceis.t1 VALUES (099001)
INSERT INTO mceis.t1 VALUES (100001)
INSERT INTO mceis.t1 VALUES (101001)
INSERT INTO mceis.t1 VALUES (099034)
INSERT INTO mceis.t1 VALUES (100066)
INSERT INTO mceis.t1 VALUES (101098)
INSERT INTO mceis.t1 VALUES (101198)
INSERT INTO mceis.t1 VALUES (101298)
INSERT INTO mceis.t1 VALUES (101365)

Notice that I used the SQL naming convention. The collection and table names
are separated by a period (.). Be sure to use a forward slash (/) instead of
a period if you're using SYS naming convention.

OK, now that we have some data, let's work on how to make the string we
need. The first step is to convert the CYY representation of the year to a
four-digit year:

DECIMAL(d +1900000,7,0))

The next step is to convert the seven-digit decimal number to a character
string. The DIGITS function does this nicely:

DIGITS(DECIMAL(d +1900000,7,0)))

Here is an example statement using the transformation and the resulting
data:

SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0)) ) AS CONVDATE
FROM mceis.t1

R#

D

CONVDATE


1

99001

01/01/1999


2

100001

01/01/2000


3

101001

01/01/2001


4

99034

02/03/1999


5

100066

03/06/2000


6

101098

04/08/2001


7

101365

12/31/2001

Now, let's use the formula in a common table expression and then use the
resulting field in a number of expressions:

SELECT D, CONVDATE,
DAYOFWEEK(CONVDATE) AS DAYOFWEEK,
QUARTER(CONVDATE) AS QUARTER,
CONVDATE - 31 days AS DMINUS31,
MONTH(CONVDATE) AS MONTH,
days(CURRENT_DATE)-days(CONVDATE) AS ELAPSED_DAYS
FROM (SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0))) AS CONVDATE
FROM mceis.t1) AS X

Notice that an SQL statement is nested within a FROM clause. The inner SQL
statement runs first; yielding a temporary table I call X. The outer SELECT
then runs against this temporary table.

The above statement would yield the following result table from the sample
data:


R#

D

CONVDATE

DAYOFWEEK

QUARTER

DMINUS31

MONTH

ELAPSED_DAYS


1

99001

01/01/1999

6

1

12/01/1998

1

1330


2

100001

01/01/2000

7

1

12/01/1999

1

965


3

101001

01/01/2001

2

1

12/01/2000

1

599


4

99034

02/03/1999

4

1

01/03/1999

2

1297


5

100066

03/06/2000

2

1

02/04/2000

3

900


6

101098

04/08/2001

1

2

03/08/2001

4

502


7

101365

12/31/2001

2

4

11/30/2001

12

235



So, using the date data type can open up a world of calculations against
your data. I hope this helps.

-- Howard



Howard F. Arner, Jr. is a writer and consultant for Client Server
Development, Inc <http://www.sqlthing.com> . You can purchase Howard's
book, "iSeries and AS/400 SQL at Work," from www.sqlthing.com/books or visit
www.sqlthing.com to find out more about manipulating dates on the AS/400.



_____

From: [email protected] [mailto:[email protected]] On
Behalf Of sally_earnest
Sent: Monday, September 26, 2005 3:49 PM
To: [email protected]
Subject: Re: Julian to Gregorian date conversion in EXCEL



Any SQL magic for Gregorian (specifically today's date) converted to the JDE
Julian CYYDDD?

Mohawk Industries, Inc. JDE A7.3 C14, AP,GL,AP,PO,IN...

_____


The entire <http://www.jdelist.com/ubb/showflat.php?Cat=&Board=> JDELIST
thread is available for viewing.


Looking for a job? Check out the Job
forum


This is the JDELIST World Mailing List.
The instructions on how to unsubscribe from any JDELIST mailing list are
available here <http://www.jdelist.com/unsubscr.shtml> .
JDELIST is not affiliated with JDEdwardsR.

.

In Excel replace [IXEFFF] with the cell reference for the JDE Julian. This
formula is from Access.

=DateValue("12/31/" &
(Int([IXEFFF]/1000))+1899)+([ixefff]-Int([ixefff]/1000)*1000)

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



miaconis
<miaconis@soartec
hsolutions.com> To
Sent by: [email protected]
jdeworld-bounces@ cc
jdelist.com
Subject
RE: Julian to Gregorian date
09/27/2005 06:28 conversion in EXCEL
AM


Please respond to
JD Edwards® World
<jdeworld@jdelist
.com>






I found a function from one of my other email lists a couple of years ago.
I'll try to cut and paste the info here.



Convert J.D. Edwards Dates to Other Formats



Hey, Howard:

I want to convert a J.D. Edwards date, (CYYDDD where C represents the
century, YY represents the year, and DDD represents the day of the year) to
a more readable format, (like YYYY-MM-DD). I can do this in Crystal
Reports,
but is there a way to do it in SQL?




-- Domenic

This is a good one, Domenic, as I first thought it was impossible, but on
further reading of the IBM documentation, I found a
little known use for the DATE function. I found that DATE can take a
variety
of arguments. As I was reading the documentation on DATE, I noticed that
one
of the acceptable date formats is a seven-character string in the form
YYYYNNN where YYYY is the year and NNN is the day number. When passed !
this
information, DATE will return a date data type value representative of the
string that was passed in.

To take advantage of this feature, we need only to convert your
DECIMAL(6,0)
representation of the date into the required CHAR(7) representation and
pass
it to the date function. Let's do it!

First we create some test data:

CREATE TABLE mceis.t1 (d DECIMAL(6,0))
INSERT INTO mceis.t1 VALUES (099001)
INSERT INTO mceis.t1 VALUES (100001)
INSERT INTO mceis.t1 VALUES (101001)
INSERT INTO mceis.t1 VALUES (099034)
INSERT INTO mceis.t1 VALUES (100066)
INSERT INTO mceis.t1 VALUES (101098)
INSERT INTO mceis.t1 VALUES (101198)
INSERT INTO mceis.t1 VALUES (101298)
INSERT INTO mceis.t1 VALUES (101365)

Notice that I used the SQL naming convention. The collection and table
names
are separated by a period (.). Be sure to use a forward slash (/) instead
of
a period if you're using SYS naming convention.

! OK, now that we have some data, let's work on how to make the string we
need. The first step is to convert the CYY representation of the year to a
four-digit year:

DECIMAL(d +1900000,7,0))

The next step is to convert the seven-digit decimal number to a character
string. The DIGITS function does this nicely:

DIGITS(DECIMAL(d +1900000,7,0)))

Here is an example statement using the transformation and the resulting
data:

SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0)) ) AS CONVDATE
FROM mceis.t1

R#

D

CONVDATE


1

99001

01/01/1999


2

100001

01/01/2000


3

101001

01/01/2001


4

99034

02/03/1999


5

100066

03/06/2000


6

101098

04/08/2001


7

101365

12/31/2001

Now, let's use the formula in a common table expression and then use the
resultin! g field in a number of expressions:

SELECT D, CONVDATE,
DAYOFWEEK(CONVDATE) AS DAYOFWEEK,
QUARTER(CONVDATE) AS QUARTER,
CONVDATE - 31 days AS DMINUS31,
MONTH(CONVDATE) AS MONTH,
days(CURRENT_DATE)-days(CONVDATE) AS ELAPSED_DAYS
FROM (SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0))) AS CONVDATE
FROM mceis.t1) AS X

Notice that an SQL statement is nested within a FROM clause. The inner SQL
statement runs first; yielding a temporary table I call X. The outer SELECT
then runs against this temporary table.

The above statement would yield the following result table from the sample
data:


R#

D

CONVDATE

DAYOFWEEK

QUARTER

DMINUS31

MONTH

ELAPSED_DAYS


1

99001

01/01/1999

6

1

12/01/1998

1

1330


2

100001

01/01/2000

7

1

12/01/1999 !

1

965


3

101001

01/01/2001

2

1

12/01/2000

1

599


4

99034

02/03/1999

4

1

01/03/1999

2

1297


5

100066

03/06/2000

2

1

02/04/2000

3

900


6

101098

04/08/2001

1

2

03/08/2001

4

502


7

101365

12/31/2001

2

4

11/30/2001

12

235



So, using the date data type can open up a world of calculations against
your data. I hope this helps.

-- Howard



Howard F. Arner, Jr. is a writer and consultant for Client Server
Development, Inc . You can purchase Howard's
book, "iSeries and AS/400 SQL at Work," from www.sqlthing.com/books or
visit
www.sqlthing.com to find out more about! manipulating dates on the AS/400.



_____

From: [email protected] [mailto:[email protected]] On
Behalf Of sally_earnest
Sent: Monday, September 26, 2005 3:49 PM
To: [email protected]
Subject: Re: Julian to Gregorian date conversion in EXCEL



Any SQL magic for Gregorian (specifically today's date) converted to the
JDE
Julian CYYDDD?

Mohawk Industries, Inc. JDE A7.3 C14, AP,GL,AP,PO,IN...

_____


The entire JDELIST
thread is available for viewing.


Looking for a job? Check out the Job
forum


This is the JDELIST World Mailing List.
The instructions on how to unsubscribe from any JDELIST mailing list are
available here .
JDELIST is not affiliated with JDEdwardsR.

.

The "SQL magic" is going to depend on the what machine you run the SQL
statement (not to be confused with which database you are getting the data
from, unless you are doing passthru queries)

On the AS400 (ISeries)
year(now()) would return YYYY
dayofyear(now()) would return DDD
format the 2 together & you are done

(year(now())-1900)*1000+dayofyear(now())



Using a VB function you could do: and pass today as the "mydate" value
Function Date2Julian(MyDate)
Date2Julian = Val(((year(MyDate) - 1900) * 1000) + (MyDate -
DateSerial(year(MyDate) - 1, 12, 31)))
End Function




Dan Verner
American NTN Bearing Mfg. Corp (ANBM)
Information Technology Manager

Tel : (847) 622-4553
Fax : (847) 888-1226
Mail : [email protected]



sally_earnest
<sally_earnest@moha To: [email protected]
wkind.com> cc:
Sent by: Subject: Re: Julian to Gregorian date conversion in EXCEL
jdeworld-bounces@jd
elist.com


09/26/2005 02:49 PM
Please respond to
JD Edwards® World






Any SQL magic for Gregorian (specifically today's date) converted to the
JDE Julian CYYDDD?


Mohawk Industries, Inc. JDE A7.3 C14, AP,GL,AP,PO,IN...

DATE(INT((1900000+C2)/1000),1,C2-INT(C2/1000)*1000)

…where C2 is a cell name containing date in Julian format that you want convert.

see Julian Date Converter