7 Replies Latest reply on Dec 1, 2012 3:54 PM by Malcolm

# Julian Date (six digit JDEdwards) Conversion

Included below is the formula for converting the date (from JDEdwards/PeopleSoft Accounting system) format from Julian (CYYDDD = Standard Julian Date Format) to a "regular" date in Excel...I'm not sure how to convert the Excel formula into a Filemaker calculation (or even better, a custom function).

C = Century Code: This segment is a single digit number where 1 represents a date year of 2000 or later, while a 0 represents a year before or equal to 1999. For dates with a year before or equal to 1999, the leading 0 may or may not be present for all field values.

YY = Year: This segment is a two digit number indicating the year of the date.

DDD = Day of the year: This three digit segment represents the day of the year. This number can range from the first of the year as 001, to the last day of the year as 365 (or 366 for leap years).

Here's a sample of what the calculation should do: Convert the six digit number 109001 to January 1, 2009 (or 1/1/2009 - whatever...just the standard date format).

Excel 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

I know it works in Excel, if someone can get it into proper Filemaker format I would be very grateful!

- Doug

• ###### 1. Re: Julian Date (six digit JDEdwards) Conversion

Included below is the formula for converting the date (from JDEdwards/PeopleSoft Accounting system) format from Julian (CYYDDD = Standard Julian Date Format) to a "regular" date in Excel...I not sure how to convert to a Filemaker calculation (or even better, a custom function).

C = Century Code:  This segment is a single digit number where 1 represents a date year of 2000 or later, while a 0 represents a year before or equal to 1999.  For dates with a year before or equal to 1999, the leading 0 may or may not be present for all field values.

YY = Year:  This segment is a two digit number indicating the year of the date.

DDD = Day of the year:  This three digit segment represents the day of the year.  This number can range from the first of the year as 001, to the last day of the year as 365 (or 366 for leap years).

Let ( [

julian = "098237";

C = getasnumber( left(julian ; 1 ) ) ;

YY = middle ( julian ; 2 ; 2 ) ;

DDD = right ( julian ; 3 ) ;

Yr = 1900 + YY + ( C * 100 )

] ;

Date( 1 ; 1 ; Yr ) - 1 + DDD

)

Malcolm

1 of 1 people found this helpful
• ###### 2. Re: Julian Date (six digit JDEdwards) Conversion

Try =

Let ( [

DDD = Mod ( JuianDate ; 10^3 ) ;

YY = Mod ( Div ( JuianDate ; 10^3 ) ; 10^2 ) ;

C = Mod ( Div ( JuianDate ; 10^5 ) ; 10^1 )

] ;

Date ( 1 ; DDD ; Choose ( C ; 1900 ; 2000 ) + YY )

)

---

Or, if you prefer a text manipulation =

Let ( [

string = SerialIncrement ( "000000" ; JuianDate ) ;

C = Left ( string ; 1 ) ;

YY = Middle ( string ; 2 ; 2 ) ;

DDD = Right ( string ; 3 )

] ;

Date ( 1 ; DDD ; Choose ( C ; 1900 ; 2000 ) + YY )

)

Message was edited by: Michael Horak

• ###### 3. Re: Julian Date (six digit JDEdwards) Conversion

I am afraid you have missed this part:

"the leading 0 may or may not be present"

• ###### 4. Re: Julian Date (six digit JDEdwards) Conversion

There are already several custom functions for Julian date conversion (to and from):

http://www.briandunning.com/filemaker-custom-functions

enter "Julian" in the search box and see what appears!

Beverly

• ###### 5. Re: Julian Date (six digit JDEdwards) Conversion

Beverly, I scrounged the Dunnning sight, and I'm sure it's because I'm lame, couldn't get either of the ones I tried to work and gave up trying there. But yes, that is a good suggestion, thanks!

1 of 1 people found this helpful
• ###### 6. Re: Julian Date (six digit JDEdwards) Conversion

Thanks Malcom and Michael. I ended up using Michael's first solution and it works perfectly. THANKS AGAIN!

• ###### 7. Re: Julian Date (six digit JDEdwards) Conversion

I ignored it.

I'm glad that you put DDD directly into the date function. It's much clearer.