Forum Discussion

jpt1228's avatar
jpt1228
Responsive Resident
8 years ago
Solved

Julian Date Conversion or Format

Hello, I searched and read the 2 posts about julian dating but I can't get a solution to work.   I can either convert the julian date column on my inventory table YYDDD into a calendar date or add ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Sorry, my mistake.

     

    Please use this instead

     

    Column = 
VAR myYear = 2000 + INT(DIVIDE([Julian Manufacture Date],1000))
VAR myDayOfYear = MOD([Julian Manufacture Date],1000) -1
RETURN DATE( myYear,1,1) + myDayOfYear
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi jpt1228

 

THis might be close as a new calculated column

 

Column = 
VAR myYear = INT(DIVIDE([Julian Manufacture Date],1000))
VAR myDayOfYear = MOD([Julian Manufacture Date],1000) -1
RETURN DATE(Year(myYear),1,1) + myDayOfYear

 

jpt1228's avatar
jpt1228
Responsive Resident
8 years ago

Phil_Seamark

 

Thanks - But getting an error when I add column into inventory table

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI jpt1228

     

    I think you are missing the top line.  You need the following.

     

    column = 

    • jpt1228's avatar
      jpt1228
      Responsive Resident
      8 years ago

      Phil_Seamark Yes, that was it - Works for the Month/Day but the year is showing 1900 not 2017

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        What is the raw format in Julian for the 1st of Jan, 1900 ?

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    I guess you need to add table name next to "Julian Manufacture Date"