Forum Discussion
Solved
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 ...
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)
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.
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)