cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 7 digits Julian date to calenderdate

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

1 ACCEPTED SOLUTION
Frequent Visitor

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

5 REPLIES 5
Frequent Visitor

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

Community Support

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.

Frequent Visitor

#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?

Super User

@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

Frequent Visitor

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 😫