Convert Julian Date to Calendar Date

[DarylM]

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

Struggling with a Dax formula for this. 

Thanks!

[bidgeir]

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

[khickerson]

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]

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.

[DarylM]

I did find a formula in DAX and M that works create in converting a 6 digit julian to a date. 

DAX

DATE(INT(BISalesView[Date Julian]/1000)+1900,1,MOD(BISalesView[Date Julian],1000))

M Language (Query Editor)

Date.AddDays(#date(Number.RoundDown([Date Julian]/1000)+1900,1,1),Number.Mod([Date Julian],1000)-1)

Hope this helps!

[I_Like_Pi]

That was the simplification I was looking for @DarylM 

Thanks

[leniorcortivo]

Perfect, it works exactly how i needed. Thanks!

[DarylM]

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 · Accepted solution]

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