Forum Discussion

DarylM's avatar
DarylM
Icon for Helper II rankHelper II
9 years ago
Solved

Convert Julian Date to Calendar Date

Does anyone know how I can convert a Julian date (Example "116343") to a calendar date? 

Struggling with a Dax formula for this. 

 

Thanks!

Need Help
  • bidgeir's avatar
    bidgeir
    9 years ago

    Yes you can reference a column name instead of a constant. It is what is recommended.

8 Replies

  • bidgeir's avatar
    bidgeir
    Icon for Most Valuable Professional rankMost Valuable Professional
    9 years ago

    As I understand Julian date the first two numbers are the year and the last 3 represent the day of the year. If that is true you could do somthing like below where you create a date from the numbers. The below formula only counts for dates between 2000 and 2099 because of the hardcoded "20" but you can use it as a start if it works for you. This is by the way done in M or the custom column in the query editor, not in DAX. Hope that is ok

     

    Date.AddDays(Date.From(DateTimeZone.From("20" & Text.Start(Number.ToText(116343),2) & "-01-01")),Int64.From(Text.End(Number.ToText(116343),3)))

     

    Hope you can use it for something

    • DarylM's avatar
      DarylM
      Icon for Helper II rankHelper II
      9 years ago

      Thank you.

      I was incorrect on something though in my example. the date is accually afive digit code (16343). I was not abel to modify your formula to work with the fivei digits, Also, I would need this to work in a table and column of many julian dates. Can I reference the column name rather than the Julian date in the forumla?

      • bidgeir's avatar
        bidgeir
        Icon for Most Valuable Professional rankMost Valuable Professional
        9 years ago

        Yes you can reference a column name instead of a constant. It is what is recommended.

    • khickerson's avatar
      khickerson
      New Member
      9 years ago

      I found this string while trying to convert from a 7-digit ordinal date to a Gregorian Date.  I repurposed the formula as shown below (changes in green) and thought it was working beautifully until I realized that all of my dates are 1 day later.  This is because the 3-digit number-of-days is being added to "01."  I have tried to resolve the problem, but I keep getting errors.  I am not M Code literate.  Any advice on how this can be amended?

       

      Thanks!

       

      Date.AddDays(Date.From(DateTimeZone.From("20" & Text.Start(Number.ToText([column reference]),4) & "-01-01")),Int64.From(Text.End(Number.ToText([column reference]),3)))

      • I_Like_Pi's avatar
        I_Like_Pi
        Icon for Resolver II rankResolver II
        8 years ago

        Is this correct? Consider January first (Julian 16001)  I think you would need to Minus 1 before your days add.

        Sorry if thats a distraction. I found this post while looking for a more elegant conversion, as I already use something similar. something that includes dropping a day from the days add.