Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Frigge__
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
Frigge__
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

View solution in original post

5 REPLIES 5
Frigge__
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

v-xiaotang
Community Support
Community Support

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

 

 

amitchandak
Super User
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

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 😫

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors