cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
medwardsOT
Regular Visitor

7 Digit Julian Date conversion

Hellowive looked for an answer regarding this but only ones regarding different julian forms.

 

I have a date field from our 400 server thats in 7 digit date format.

Example: 2016005

That emaple is needs to be written into a normal date format mm/dd/yyyy (1/5/2016) The days are number 1-365.

IM trying to edit this field so when the data is queried it will come in this form autopmatically.

 

What DAX code should i use to convert this? We are adding a new column and trying to use code there if that helps.

 

Thanks

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

If a query (M) solution is also fine, then you can add a custom column with the following code, in which "JulianDate" is your field with the 7-digit date:

 

= Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)
Specializing in Power Query Formula Language (M)

View solution in original post

12 REPLIES 12
medwardsOT
Regular Visitor

Thanks everyone . @MarcelBeug Your version worked perfect! 

MarcelBeug
Community Champion
Community Champion

If a query (M) solution is also fine, then you can add a custom column with the following code, in which "JulianDate" is your field with the 7-digit date:

 

= Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)
Specializing in Power Query Formula Language (M)

View solution in original post

Is there a way to remove the error if there is no number in the field? 

I would prefer preventing the error, e.g. if "no number" means null

 

= if [JulianDate] = null then null else Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)

 

If the error can not be prevented, then you can use try .. otherwise, like

 

= try Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1) otherwise null

 

Note: I adjusted the code out of my head, so not tested. Hopefuly it's correct.

Specializing in Power Query Formula Language (M)

I thought there was no number. It actually had a 0 so i modified it

if [DRTDAT] = 0 then " " else Date.AddDays(#date(Number.RoundDown([DRTDAT]/1000),1,1),Number.Mod([DRTDAT],1000)-1)

 

Sean
Community Champion
Community Champion

@MarcelBeug  This is by far my favorite answer of the day!

 

Can you explain what the number in red does?

 

= Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)

Thanks!

 

Date.AddDays and  Number.RoundDown and Number.Mod

 

MarcelBeug
Community Champion
Community Champion

Hi @Sean,

 

The first 1 is the 3rd argument (day part) for #date.

Without it, you get an error message: Expression.Error: 2 arguments were passed to a function which expects 3.

 

The second 1 is a correction as the function adds the days from the year-day-number (1-365) to the date of January 1st.

Without it, 2017001 would become January 2, 2017.

 

Actually I'm a bit surprised this would be such a remarkable formula, but anyway: thanks!

 

Specializing in Power Query Formula Language (M)
Sean
Community Champion
Community Champion

@MarcelBeugThanks!

 

Maybe because it takes me 4 steps (3 conditional columns) and then in the 4th concatenate the final result to do this with DAX.

Split the number into year and the remaining 3 digits - Check if its a Leap Year

and then 2 long switch statements to convert the 3 digits - once into months and then - into days in each month

depending on whether it is a leap year or not

and then I concatenate and convert to Data Type: Date

 

But then again there may be an easier DAX solution

 

If anyone knows of one it may be @OwenAuger or @KHorseman or @Anonymous or @Vvelarde

Hi @Sean

 

I would follow logic similar to @MarcelBeug but slightly different:

 

CalendarDate =
DATE ( INT ( DIVIDE ( TheTable[JulianDate], 1000 ) ), 1, MOD ( TheTable[JulianDate], 1000 ) )

Or this version would be about the same as Marcel's.

CalendarDate =
DATE ( INT ( DIVIDE ( TheTable[JulianDate], 1000 ) ), 1, 1 )
    + MOD ( TheTable[JulianDate], 1000 )
    - 1

In this case, with the DATE function in DAX (and Excel for that matter) you can get away without explicitly working out the months/days.

It will automatically "roll over" the years/months as required if either the month argument is outside 1..12 or the day argument is outside the date range of the month. 

 

For example DATE(2016, 1, 100) is equivalent to DATE(2016, 4, 9), i.e. the 100th day counting from 1st Jan 2016.

 

Cheers,

Owen 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn
Sean
Community Champion
Community Champion


@OwenAuger wrote:

For example DATE(2016, 1, 100) is equivalent to DATE(2016, 9, 4), i.e. the 100th day counting from 1st Jan 2016.



 

As always @OwenAuger - that works great! Thank You! Smiley Happy

 

Indeed the DATE function takes care of this I did not know/remember this!

 

the 100th day in 2016 - April 9, 2016 - DATE ( 2016, 1, 100 )

the 100th day in 2015 - April 10, 2015 - DATE ( 2015, 1, 100 )

 

Thanks Owen!

Sean

Sean
Community Champion
Community Champion

Just heads up - 2016 was a leap year!

 

Good Luck! Smiley Happy

Yes it was but i will need it for previous years as well.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.