7 digits Julian date to calenderdate

[Frigge__]

Hello,

I'm accessing a local DB to set up my report. It's an old DB so date format changes have been made during the years. 

Some dates are in timestamps eg 1380146400 = 2013-09-25 22:00:00 (no problem customizing a column for that).

But then!
These dates give me a hard time:

2456685 that should be 2014-01-27 12:00:00

2454110 should be 2007-01-09 12:00:00

2459572 should be 2021-12-23 12:00:00

The time is not relevant just the date. 

I get the correct value from https://www.onlineconversion.com/julian_date.htm

But how do I get this to work in a custom column i PowerBi?

//BR

[Frigge__ · Accepted solution]

So I solved this first by calculating the number of days from -4713/01/01 to 1970/01/01 and subtracting it from the known date to a new column. Then from 1970/01/01 add those days in the new column.

"if [dat_dv] = null then null else [dat_dv] - 2440588"

"if [DVconv] = null then null else #date(1970,1,1) + #duration([DVconv],0,0,0)"

Maybe not the most elegant solution, but it works when in Power query. 

BR

[v-xiaotang]

Hi @Frigge__ 

About the Conversion algorithm, you can refer to this article,

https://docs.kde.org/trunk5/en/kstars/kstars/ai-julianday.html

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Frigge__]

#UPDATE

So I found out that the numbers originate from the Gregorian calendar (?). That means the numbers are days from January 1st 4713 BC.

So how can I add 2459572 days to January 1st 4713 BC and get the correct date in a new column? 

[amitchandak]

@Frigge__ , I see a power Query solution. But your number seems, little bigger compare to the example

https://community.powerbi.com/t5/Desktop/Convert-Julian-Date-to-Calendar-Date/m-p/99936

[Frigge__]

Hi, 

I have tried it, but that won't work.

The attached link is the only "converter" that is working, but I can't figure out how 😫