Convert Julian Dates to Gregorian Dates with Power Query

The question of how to convert Julian dates to Gregorian dates using Power Query came up today in a course I was teaching. Although I haven't ever used JD Edwards myself, apparently this is quite common in that system, so it hits a lot of people. In this article, we will look at the method to do so.

What the Heck is a Julian Date Anyway?

The "standard" calendar around the world in most cultures today is the Gregorian calendar, which actually replaced the Julian calendar due to the latter's "drift" on leap years. To keep it short, the Julian calendar is an antiquated calendar that doesn't handle leap years the same as we do today. So why would anyone care about this at all? Well, as it turns out, the JD Edwards system actually uses it. And if you happen to be unlucky enough to need to import this data into Excel or Power BI... you're going to need to learn how to convert Julian dates to Gregorian dates.

The JD Edwards Julian Date Implementation

The key to doing this is to understand the methodology that JD Edwards uses to report a Julian date. In this system every date will be presented to you as a 6 digit number. A couple of examples are 098185 or 122350. To work with this, you'll need to understand that this is essentially an encoded date in the format C-YY-DDD where:

• C represents the Century. Add 19 to the value you have there and you will have the date's century. I.e. 0 = 1900 and 1 = 2000.
• YY represents the decade and year.
• DDD represents the numeric day of the year, which will range from 1 to 366, depending on whether or not it is a leap year.

The Logic to Convert Julian Dates to Gregorian Dates

So to calculate the Gregorian date, we can start by breaking apart the pieces, then work out which year we have:

Julian Date	C	YY	DDD	Actual Year
098185	0	98	185	(C+19)*100+YY = 1998
122350	1	22	350	(C+19)*100+YY = 2022

The next step is to generate the actual dates. To do this, we take Jan 1 of each of the years, add the number of days from the DDD column, and subtract 1 (since the Jan 1 shows a value of 1, not 0):

Julian Date	C	YY	DDD	Start of Year (SOY)	Date
098185	0	98	185	2019-01-01	SOY + DDD -1 = Jul 4, 1998
122350	1	22	350	2022-01-01	SOY + DDD -1 = Dec 16, 2022

Convert Julian Dates to Gregorian Dates using Power Query

Now that we know the logic, how do we do this in Power Query? Let's assume that we have connected to a data source, and have received the following Julian dates:

The first thing we need to do is to split this into the components that represent the C, YY and DDD columns. The tricky part is that Power Query will often look at this data and turn it into a whole number format, dropping any leading zeros. For that reason, we are going to have to make sure we split our values from right to left, in order to make sure we always end up with 3 digit DDD columns and 2 digit YY columns.

The steps to do this are:

• Set the Data type for the column to Text (to allow using the Split commands)
• Select the column -> Transform -> Split Column -> By Number of Characters
• Choose to split by 3 characters Once, as far right as possible
• Select the first column (Julian Date 1.1)
• Select the column -> Transform -> Split Column -> By Number of Characters
• Choose to split by 2 characters Once, as far right as possible
• Rename the columns as "C", "YY" and "DDD"

The data will now look like this:

At this point you should notice that we lost the 0 for the first record of the C column. (This is due to Power Query automatically thinking an intermediary step generated a column of numbers, so it got rid of the leading zero.) This is pretty easy to fix:

• Right click the C column -> Replace Values -> Replace null with 0

Now we need to generate a "Start of Year" column. To do this:

• Go to Add Column -> Custom Column
• Call the new column "Start of Year" and use the following formula:

Date.From( Text.From( 19 + [C] ) & Text.From([YY]) & "-01-01" )

The end result should be a column of dates representing Jan 1 of each year. (Note that they may not show in the same format as mine.)

Now, it's time to generate the Gregorian date:

• Go to Add Column -> Custom Column
• Call the new column "Date" and use the following formula:

Date.AddDays( [Start of Year], [DDD] - 1 )

• Select any unnecessary columns -> right click -> Remove Other Columns
• Set the data type for the Date column to a Date

Given that I chose to only keep the date column, you can see the final output here:

So there you go! Once you know the logic, it's really not too hard to make it work. If you'd like to see it all in action, download the sample workbook here.

After reading this article, one of my readers (Pieter S) emailed me to let me know of a formula to generate the year portion, which replicates =1900 + FLOOR(value/1000).

Convert Julian Dates to Gregorian Dates via a Power Query Formula

Note that it appears a bit long, but this is to cater to issues where the original Julian Date column gets (automatically) formatted as a whole number, and loses leading zeros.

Date.AddDays(
	#date(
		1900 + 
		Number.From(
			Text.Start(
				Text.PadStart(
					Text.From([Julian Date]),
					6,
					"0"
				),
				3
			)
		),
		1,
		1
	),
	Number.From(
		Text.End(
			Text.From([Julian Date]),
			3
		)
	) - 1
)

A Function to Easily Convert Julian Dates to Gregorian Dates

If you're reading this article, chances are that this isn't the last time you'll need to do this kind of work. If you'd like to save yourself yourself the trouble of taking all of these steps manually, why not just copy my fxJulian_to_Gregorian function into your workbook? Here's how:

1. Download and open the Sample workbook in Excel
2. Copy the function

• Go to Data -> Queries & Connections to show the Query Pane
• Right click the fxJulian_to_Gregorain function -> Copy

3. Paste the function into your solution

Excel

• Go to the target workbook and display the Query Pane (as per above)
  • Right click some gray space -> Paste

Power BI

• Go to Home -> Transform Data (to launch Power Query)
• Right click some grey space in the Query navigator pane (on the left) -> Paste

4. Invoke the function against your dates

• Edit the query which contains your Dates
• Go to Add Column -> Invoke Custom Function
• Choose the fxJulian_to_Gregorian function
• Choose the appropriate column which holds your Julian Dates
• Name the column
• Click OK
• Set the new column as a Date data type
• Celebrate that it was super easy 🙂