Julian Date Conversion or Format

[jpt1228]

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 a column in my date table which would convert the date to the julian date.

In the below example I need 17103 to be April 13 2017 or 4/13/17

[Anonymous]

I'm converting Julian dates in JD Edwards (Oracle DB), and I had to add "1900" to the year portion of the formula.  So this is the working Power Query (M) formula I wound up with:

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

[Phil_Seamark]

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]

@Phil_Seamark

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

[Phil_Seamark]

HI @jpt1228

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

column = 

[jpt1228]

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

[Phil_Seamark]

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

[jpt1228]

@Phil_Seamark

I see where the confusion came from

00001 would be the date code for Jan 1 and could be for both 1900 and 2000 years.

The first 2 numbers are the year - 11 could be 1911, 2011, 2111

The next 3 are the day of the year - 001 would always be Jan 1. - 002 would be Jan 2 etc.

17001 would intend to be Jan 1, 2017, but could also be Jan 1, 1917.

Since this is inventory I have to measure from the date of manufacture to today to calculate how old the inventory is. I was going to convert the 

[Phil_Seamark · Accepted solution]

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

[pludlow]

HI, when I try this formula, I am getting a Token Eof expected error

[jpt1228]

Hi @pludlow. Looks like a formatting error on your date see https://community.powerbi.com/t5/Desktop/Token-eof-expected/td-p/27929

[pludlow]

Hi @jpt1228, still confused.  I am pulling data straight of JDE and coming out in this format.  I have tried several formulas and

none seem to work.  Below is the best one so far

[pludlow]

I was able to solve my issue using the formula I used above but reformatting the date

[jpt1228]

Thanks @Phil_Seamark!

[parry2k]

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

[parry2k]

well easier to add julian date in calendar table and set relationship on julian date between date and inventory table.

It can be handly to have in date table , in case you hve another table with julian dte, you don't need to convert it to date rather use julian date.