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

    Julian Date (six digit JDEdwards) Conversion

    surfingmussel

      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