Converting Julian to Gregorian Dates in Power Query M

In this article, we will look at different ways to convert between Julian Dates and Gregorian Dates. We will look at different Julian Dates, including the JD Edwards implementation, and then learn how to convert the formats between Julian and Gregorian.

Ken Puls did something similar, converting Julian Date to Gregorian, but this article aims to cover more formats to convert between both the Julian and the Gregorian date

Table of contents

• Introduction to Julian Dates
• Working with Julian Date (JD Edwards)
  • Converting CYYDDD into Gregorian Date 
  • Converting Gregorian Date into CYYDDD
• Working with Extended Julian Date
  • Converting YYYYDDD into Gregorian Date 
  • Converting Gregorian Date into YYYYDDD
• Working with Simplified Julian Date
  • Converting YYDDD into Gregorian Date 
  • Converting Gregorian Date into YYDDD
• Conclusion

Introduction to Julian Dates

In this article we’ll delve into three versions of the Julian date:

• JD Edwards (JDE) World Julian Date format (CYYDDD) is a measure used by Oracle’s JD Edwards Enterprise EnterpriseOne software. It is used for convenience in inventory management and manufacturing, among others. It simplifies date tracking within a single year.
• Extended Julian Date: The format for the extended Julian date is YYYYDDD. Suppose you have the year 1917, it would be represented by the Julian date of ‘1917091’. Here ‘1917’ is the year, whereas ‘091’ represents the 91st day of the year, which is April 1st in a non-leap year.
• Simplified Julian Date: It uses similar logic as the Extend Julian Date, except the year is now represented by two digits. The format of this is YYDDD The simplified Julian date for the year 1917 is ‘17091’. The value ’17’ represents the year (1917 in this case), and again ‘091’ represents the 91st day of the year. Because it only uses 2-digit years, the simplified Julian date can only be used for date ranges within the same century.

Working with Julian Date (JD Edwards)

Converting CYYDDD into Gregorian Date 

The JD Edwards Julian Date adopts the structure CYYDDD for date representation.

In this structure:

• ‘C’ represents the century’s increment above 1900. For instance, ‘0’ would correspond to the 1900s, and ‘1’ to the 2000s.
• ‘YY’ indicates the year within that century.
• ‘DDD’ signifies the sequential day of the year, spanning from 001 to 365 or 366 in a leap year, and potentially up to 731 to account for the subsequent year.

To convert a JD Edwards Julian Date to the commonly used Gregorian calendar date, apply the following steps:

let
  // Retrieves the Julian date text from the column named 'MJ'.
  JulianText = [MJ], 

  // Extracts the first character of the Julian date, converts it to a number, 
  // adds 19 to determine the full century (e.g., '0' becomes 1900, '1' becomes 2000).
  Century = Number.From(Text.Start(JulianText, 1)) + 19, 

  // Takes the next 2 characters from the Julian date to get the century year.
  Year = Number.From(Text.Middle(JulianText, 1, 2)), 

  // Takes the last three characters to get the day of the year.
  DayOfYear = Number.From(Text.End(JulianText, 3)), 

  // Calculates the date by first creating a new date at the start of the century,
  // then adding the year and finally the days.
  DateValue = 
    Date.AddDays(
      Date.AddYears(
        #date(Century * 100, 1, 1), // Creates date representing January 1st of century.
        Year // Adds the year within the century to the base date.
      ), 
      DayOfYear - 1 // Adds day of year to the base date, adjusting by subtracting 1.
    )
in
  DateValue // Outputs the final computed Gregorian date.

This will give you:

Should you encounter instances where the data source mistakenly interprets these values as numbers, stripping away leading zeros, the issue can be fixed with the expression below:

Number.ToText( 
  [#"Julian Date (JDE)"], 
  "000000" 
)

Using this expression transforms Julian date values that are formatted as numbers into a text string, restoring any leading zeros needed to extract the correct date.

Converting Gregorian Date into CYYDDD

There are times when the task at hand requires the opposite of our previous conversion—turning a standard Gregorian date back into the JD Edwards Julian Date format (CYYDDD). For these instances, you’ll need a reliable method to reverse the transformation. The steps below will guide you through this process.

let
  // Assigns the date from your column to a variable.
  GregorianDate = [Date], 

  // Extracts the century from the year, subtracts 19, and convert to text.
  // This identifies the 'C' in CYYDDD,
  Century = Number.ToText ( ( Date.Year ( GregorianDate ) / 100 ) - 19, "0" ),
 
  // Extracts the last 2 digits of the year to get the year within the century 
  // and ensure it is in a two-digit format with leading zero if necessary.
  CenturyYear = Number.ToText ( Number.Mod ( Date.Year ( GregorianDate ), 100 ), "00" ), 

  // Converts day of the year into a 3-digit number with leading zeros if necessary.
  DayOfYear = Number.ToText ( Date.DayOfYear ( GregorianDate ), "000" ), 

  // Concatenates the century, century year, and day of year to form the Julian date.
  JulianDate = Century & CenturyYear & DayOfYear 
in
  JulianDate // Outputs the final Julian date string.

Working with Extended Julian Date

The Extended Julian Date format (YYYYDDD) expresses a date as a seven-digit integer, with the first four digits denoting the year (YYYY), and the last three digits signifying the day of the year (DDD). For example, the integer 2024136 corresponds to the 136th day of the year 2024.

Converting YYYYDDD into Gregorian Date 

Suppose you have the following dataset:

To interpret an Extended Julian Date and convert it into a standard Gregorian date, the following code can be used:

  let
    YYYYDDD = [MJ],
    Year = Number.IntegerDivide ( YYYYDDD, 1000 ), 
    DayOfYear      = YYYYDDD - ( Year * 1000 ), 
    CalculatedDate = 
      Date.AddDays (
        Date.AddYears ( #date ( 1900, 1, 1 ), Year - 1900 ), 
        DayOfYear - 1
      )
  in
    CalculatedDate

In this code block:

• The YYYYDDD variable holds the Extended Julian Date.
• Year extracts the year portion of the date.
• DayOfYear computes the day within that year.
• CalculatedDate combines these to form the actual Gregorian date.

After transforming this format, this gives us:

Converting Gregorian Date into YYYYDDD

On occasions where the conversion needs to be reversed—generating an Extended Julian Date from a standard date—this expression works effectively:

let
  MyDate        = [Date], 
  YearPart      = Date.Year ( MyDate ) - 1900, 
  DayOfYearPart = Date.DayOfYear ( MyDate ), 
  JulianDate    = ( YearPart * 1000 ) + DayOfYearPart, 
  JulianNumber  = Number.ToText ( JulianDate, "000000" )
in
  JulianNumber

Here, the conversion follows these steps:

• The MyDate variable represents the input Gregorian date.
• YearPart calculates the year offset since 1900.
• DayOfYearPart extracts the day of the year from the date.
• JulianDate compiles these into the Extended Julian Date format.
• JulianNumber ensures the output is properly formatted as text.

Working with Simplified Julian Date

The Simplified Julian Date, using the YYDDD format, combines the year and day of the year into a concise five-digit number. The first two digits reflect the year, and the last three represent the day of the year.

Converting YYDDD into Gregorian Date 

Suppose you work with the following data.

To convert this format into a standard Gregorian date, apply the function outlined below:

 let
    JulianString = [YYDDD] ,
    // Change to 1900 in case looking from years since '1900'
    Year = Number.From(Text.Start(JulianString, 2)) + 2000, 
    DayOfYear = Number.From(Text.End(JulianString, 3)),
    DateValue = Date.AddDays(#date(Year, 1, 1), DayOfYear - 1)
in
    DateValue

In this function:

• The JulianString variable holds the Simplified Julian Date from the dataset.
• Year derives the full year by adding 2000 (or 1900 for 20th-century dates) to the extracted two-digit year.
• DayOfYear isolates the numerical day within that year.
• DateValue assembles the Gregorian date from these components.

The function is effective for dates within the same century.

Converting Gregorian Date into YYDDD

To turn a regular (gregorian) date into the YYDDD format, you can make use of the Date.ToText and Text.PadStart functions.

Date.ToText( [Date], "yy" ) 
  & Text.PadStart( Text.From( Date.DayOfYear( [Date] ) ), 3, "0" )

Here’s the breakdown:

• Date.ToText is used to format the date into a two-digit year string.
• Text.PadStart ensures that the day of the year is always expressed as a three-digit number, adding leading zeros if necessary.
• The concatenation of these two strings results in the final Simplified Julian Date.

Conclusion

As you’ve learned in this article, there are different approaches to working with Julian dates. There’s the JD Edwards implementation, but also the extended and simplified notation. These exist next to the the Julian Day Number as explained in this guide.

Regardless of the format, by combining functions in a smart way, you can easily transform them to and from the Julian Date format.

Happy querying!