2 Replies Latest reply: May 15, 2014 9:57 AM by Steve Caiels RSS

    Julian Date

    Jim Mathison



      I have a report that has batch entry dates in the format 2002191 (Julian) that I need to convert.  Any ideas?


      Thanks in advance,



        • Julian Date
          Tom Whiteside

          Hi, Jim!


          In this case, with your date format of "yyyyddd," the easiest solution just involves two very easy to construct lookup tables, one with 365 days and the other with 366 days.


          Make your first table by filling day numbers downward, from 1 to 365.  You can type 1 and 2 and then drag the two cells down to fill the table.  Next to this first column, fill dates downward the same way.  Since this is a 365 day table, use a non-leap year (e.g., 2001) and type in 1/1/01 and 1/2/01 and then drag these two cells down to fill all 365 dates of the year.


          Make your second table the same way, except as a 366 day, leap year table.  Your first column will fill from 1 to 366, and your second column will fill from 1/1/00 to 12/31/00 (using an example leap year of 2000).


          Now, do something like the following.  Define a YEAR=Substr(BatchDate,1,4).  I assume your BatchDate is in text, not yet in a date format.  If so, then REM=Mod(Asc(YEAR),4) will give the numeric remainder when YEAR is divided by 4.  Use a conditional lookup, so that if the remainder is zero, the leap year table is used, and if the remainder is not zero, the regular year table is used.  Have a DAYS=Substr(BatchDate,5,3) to tell what table day number to lookup.  When you retrieve the corresponding date, you can strip off the year and be left with just the month and the day - - this can now be combined with your YEAR from the BatchDate.


          Using your example of 2002191, the YEAR of this BatchDate is "2002" and is not a leap year.  Using the first lookup table, the DAYS of this BatchDate equals "191" and would return a date of 7/10/01.  Now, the Monarch Month() function will give you just the month of this reurned date, and the Monarch Day() function will give you just the day of this returned date.  Combine the YEAR, the Month(), and the Date(), and you have a usable date translation routine!


          If the explanation above is not clear, please feel free to e-mail me directly.  It's usually much easier working with specific examples in front of you.  Also, it's better to refer to your report's date format as "yyyyddd" instead of "Julian."  The real Julian calendar and date system is something different than this format, and astronomers and scientists never get tired of pointing this out.


          Good luck!




          [size="1"][ July 23, 2002, 04:42 PM: Message edited by: Tom Whiteside ][/size]

          • Julian Date
            Steve Caiels

            Or you could use a formula based solution.


            Add "01/01" to the 1st 4 characters of your field to give (for example) "01/01/2001", then add 1 less than the number of days.  If your original field is a character field called origdate, then the formula for a calculated date field would be: