Creating Julian Day Numbers with Power Query M

This article shows how you can convert dates into Julian Day Numbers using Power Query. You’ll learn both how to calculate the Julian day numbers and how to reverse-engineer them back to their original dates.

This article is part of the Date Table series, which consists of:

Understanding Julian Day Numbers

The Julian Day Number (JDN), also known as Julian Date, is a sequential count of days that serve as a universal measure of time, dating back to January 1, 4713 BC. This system simplifies the calculation of time intervals by assigning a single, continuous number to each day.

An advantage of Julian Dates lies in their simplicity: every day is marked by a unique number, starting at noon Universal Time, with fractions indicating the time passed within a day. For example, the Julian Day Number for noon on January 1, 2000, is 2451545. By converting dates into these numbers, calculating the number of days between two events becomes a matter of basic subtraction.

Julian Dates are often used by astronomers and historians, providing a clear and consistent framework to track time across centuries. This introduction sets the stage for understanding how to calculate a Julian Day Number, a process that we will explore in the following sections of the article.

Generating Julian Day Numbers

Transitioning from understanding what Julian Day Numbers are to applying this knowledge, let’s delve into the practicalities. Converting modern Gregorian dates and times into Julian Day Numbers, or vice versa, is a complex process.

Consider a set of typical Date and DateTime values from our current calendar. Our task is to transform these familiar points in time into their equivalent Julian Days.

In the next section, we will guide you through the steps to calculate Julian Day Numbers from given Gregorian Dates and DateTime values.

Converting DateTime to Julian Day Number

To transform DateTime values into Julian Day Numbers, we can use a well-established method known as the Fliegel-Van Flandern algorithm. This algorithm doesn’t merely convert dates; it incorporates the time of day to give us a complete Julian Day Number that reflects the continuous flow of time to the nearest second.

( GregorianDateTime as datetime ) as number =>
  let 
    DateTimeValue = GregorianDateTime, 
    // Convert date to Julian Day Number
    a = Number.IntegerDivide((14 - Date.Month(DateTimeValue)), 12),
    y = Date.Year( DateTimeValue ) + 4800 - a,
    m = Date.Month( DateTimeValue ) + 12 * a - 3,
    JDN = Date.Day( DateTimeValue ) + Number.IntegerDivide((153 * m + 2), 5) + 365 * y  
            + Number.IntegerDivide(y, 4) - Number.IntegerDivide(y, 100) 
            + Number.IntegerDivide(y, 400) - 32045,
    // Convert time to fraction of a day
    SecondsSinceMidnight = Time.Hour(DateTimeValue) * 3600 
                           + Time.Minute(DateTimeValue) * 60 
                           + Time.Second(DateTimeValue),
    FractionOfDay = SecondsSinceMidnight / 86400.0,
    // Julian Date is the Julian Day Number plus the fraction of a day
    JD = JDN + FractionOfDay - 0.5 // Subtracting 0.5 adjusts from midnight to noon
in
    JD

What this function does is:

1. Adjust the month and year to fit into the Julian calendar format.
2. Calculate a preliminary Julian Day Number based on the adjusted date.
3. Convert the time into a decimal to represent the fraction of the day that has passed.
4. Combine the day number and the time fraction to get a complete Julian Day Number.

By creating a custom function named fxDateToJulianDay, you can transform a column of DateTime values into Julian Day Numbers. Here’s how you can apply it:

Table.AddColumn( 
  Source, 
  "Julian Day Number", 
  each fxDateToJulianDay( [DateTime] ),
  type number
)

This function will convert DateTime values to their respective Julian Day Numbers, which include the date and the time as a decimal fraction. The added column in your table will reflect these numbers.

While this approach is comprehensive, it may be more detailed than needed for certain applications. For those instances, a simpler method might be preferable.

Converting Date to Julian Day Number

If your analysis does not require the time component of the date, a more straightforward approach can be adopted for converting dates to Julian Day Numbers. This method uses a fixed number as a reference point, from which the number of days to your specific date can be calculated.

Take the following function, which uses January 1, 2000 — a date with a known Julian Day Number of 2451545 — as the reference date. The function calculates the Julian Day for any given date by determining the number of days between it and the reference date.

( gregorianDate as date ) => 
let
    InputDate                = gregorianDate,
    KnownGregorianDate       = #date ( 2000, 1, 1 ), 
    KnownJulianDayNumber     = 2451545, 
    DaysDifference           = Duration.Days ( InputDate - KnownGregorianDate ), 
    ResultingJulianDayNumber = KnownJulianDayNumber + DaysDifference
  in
    ResultingJulianDayNumber

If you name this function fxDateToJulianDay, you can compute your Julian Day Number as follows:

Table.AddColumn( 
  Source, 
  "Julian Day Number", 
  each fxDateToJulianDay( [Date] ),
  type number
)

The simplified algorithm returns us the following table:

This function will append a new column to your table, providing a Julian Day Number for each date entry. It is efficient for working with dates postdating the year 1582, which marks the widespread adoption of the Gregorian calendar.

Converting Julian Day Number to Gregorian DateTime

When working with tables that contain Julian Day Numbers, you may need to translate these numbers back into the corresponding Gregorian DateTime values. This can be done by reversing the conversion process.

The following function accepts a Julian Day Number and computes the corresponding Gregorian DateTime:

( julianDayNumber as number ) as datetime =>
 let
    // Input your Julian Date
    JD = julianDayNumber ,
    
    // Correct for half a day offset
    JDAdjusted = JD + 0.5,
    
    // Calculate integer part of Julian Date (the day)
    Z = Number.IntegerDivide(JDAdjusted, 1),
    
    // Calculate the fraction part of the Julian Date (the time)
    F = JDAdjusted - Z,
    
    // Calculate Gregorian date components
    alpha = Number.IntegerDivide((Z - 1867216.25), 36524.25),
    A = Z + 1 + alpha - Number.IntegerDivide(alpha, 4),
    B = A + 1524,
    C = Number.IntegerDivide((B - 122.1), 365.25),
    D = Number.IntegerDivide(365.25 * C, 1),
    E = Number.IntegerDivide((B - D), 30.6001),
    Day = Number.IntegerDivide((B - D - Number.IntegerDivide(30.6001 * E, 1)), 1),
    Month = if E < 14 then E - 1 else E - 13,
    Year = if Month > 2 then C - 4716 else C - 4715,
    
    // Adjust for the fact that Julian day starts at noon
    Hours = Number.IntegerDivide(F * 24, 1),
    Minutes = Number.IntegerDivide((F * 24 - Hours) * 60, 1),
    Seconds = Number.RoundDown(((F * 24 - Hours) * 60 - Minutes) * 60, 0),
    
    // Combine components into a date-time value
    ResultDateTime = #datetime(Year, Month, Day, Hours, Minutes, Seconds)
in
    ResultDateTime

Call the function fxJulianToGregorianDateTime, and you can transform your Julian Day Number into a Gregorian DateTime value:

Table.AddColumn( 
  Source, 
  "Julian Day Number", 
  each fxDateToJulianDay( [Date] ),
  type number
)

This operation converts the Julian Day Number back to a DateTime value.

This will add a new column to your table, with each Julian Day Number converted to its equivalent Gregorian DateTime.

Converting Julian Day Number to Gregorian Date

For those instances where you have a Julian Day Number without a time component and you need to find the corresponding Gregorian date, a simplified function can be used. This function expects a number without decimals, letting go of any fractions that would represent time.

Below is the function that carries out the conversion:

( julianDate as number ) as date =>
  let
    InputJDN = julianDate,
    KnownGregorianDate = #date(2000, 1, 1),
    KnownJulianDayNumber = 2451545,
    DaysDifference = InputJDN - KnownJulianDayNumber,
    ResultingGregorianDate = Date.AddDays(KnownGregorianDate, DaysDifference)
  in
      ResultingGregorianDate

Call the above function FxJulianToGregorianDate.

To convert an entire column of Julian Day Numbers to Gregorian dates in your table, the following Power Query expression can be used:

Table.AddColumn( 
  Source, 
  "Gregorian Date", 
  each FxJulianToGregorianDate( [JDN] ),
  type date
)

Implementing this function will create a new column in your dataset where each Julian Day Number is translated into the corresponding Gregorian date without considering any time fraction.

Conclusion

To conclude, this guide showed you how to convert Date(Time) to Julian Day Numbers, and the reverse. It showed you two more complex algorithms, but also their simplified alternatives. Depending on your requirements, the simplified versions may be just as effective.

This method is not as common as the traditionally used Gregorian Calendar, but it’s great for simplifying calculations that reach over the centuries. Using these techniques, you should now be able to work effectively with Julian Day Numbers.

Happy querying!