Hi everyone, how to convert a Julian date to a calendar date in Power query? Thanks

Power Query
1

We use JDE ERP in the company, and data is from Oracle database. all the date format is Julian date. Is there a formula we can adapt in power query to change the date format?

thanks

Kristen

2

Hi Kristen,
The JDE date format is CYYDDD, where:
C is added to 19 to create the century, i.e. 0 + 19 = 19, 1 + 19 = 20.
YY is the year within the century, DDD is the day in the year.

This formula will create you a column transfering the JDE into Gregorian:

Table.AddColumn(Source, "Date", each 
#date(Number.From(Text.Start([Julian Date],1))*100+1900 + Number.From(Text.Middle([Julian Date],1,2)),1,1)+
#duration(Number.From(Text.End([Julian Date],3))-1,0,0,0), type date)

Regards,
Matthias

2 Likes