Convert Julian Date to Calendar Date in Excel

The Julian date format uses the combination of a year and the number of elapsed days of the year.

Julian dates are mostly used by manufacturers of products such as electronic products, pharmaceutical products, and food products as timestamps and batch reference numbers. They are also used in the military, astronomy, and programming.

Different manufacturers and organizations have their variations on Julian dates. For this tutorial, we will use the variation that uses 7 digits: the first four digits represent the year and the last three digits represent the total number of elapsed days in the year. For example, the Julian date for 1/1/1980 would be 1980001 and the Julian date for 31/12/2020 would be 2020366.

Consumers, service agents, or retailers can use the Julian dates from manufacturers to identify the manufacturing date of a product and thus the age of the product. Julian dates however cannot be used in calculations involving dates in Excel hence the need to first convert them into standard Calendar dates.

Three methods of converting Julian dates to standard Calendar dates

In this tutorial we will learn the following three methods of converting the 7-digit Julian dates to standard calendar dates in Excel:

  1. Use the combination of DATE, LEFT, and RIGHT functions.
  2. Use the combination of DATE, MOD, and INT functions.
  3. Use User Defined Function.

We will use the following dataset to show how the three methods can be used to convert Julian dates to standard Calendar dates:

Method 1 – Use the combination of DATE, LEFT, and RIGHT functions.

In this method, we use the combination of DATE, LEFT, and RIGHT functions to convert Julian dates to Calendar dates

The DATE function returns the number that represents the date in the Microsoft Excel date-time code. It takes three arguments and its syntax is DATE(year, month, day).

The LEFT function returns the specified number of characters from the start of a text string. It takes two arguments and its syntax is LEFT(text, [num_chars]). The num_char argument is optional and if it is not supplied, the LEFT function returns only the first character of the given text string.

The RIGHT function returns the specified number of characters from the end of a text string. It takes two arguments and its syntax is RIGHT(text, [num_chars]). The num_char argument is optional and if it is not supplied, the RIGHT function returns only the last character of the given text string.

We use the following steps to generate Calendar dates from Julian dates:

  1. Select Cell C2 and type in the formula:
 =DATE(LEFT(B2,4),1,RIGHT(B2,3))
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:

All the Julian dates in Column B are converted to standard calendar dates in Column C.

Explanation of the formula

=DATE(LEFT(B2,4),1,RIGHT(B2,3))

The LEFT function returns 4 characters of the value in cell B2 and the RIGHT function returns 3 characters.

In this case, the formula then becomes =DATE(2020,1,366). Although we supplied the value 1 for the month argument, the DATE function used the number of elapsed days in the year (366) to calculate the correct month and give the correct calendar date of 31/12/2020.

Method 2 – Use the combination of DATE, MOD, and INT functions

In this method, we use the combination of DATE, MOD, and INT functions to convert Julian dates to Calendar dates.

The DATE function returns the number that represents the date in the Microsoft Excel date-time code. It takes three arguments and its syntax is DATE(year, month, day).

The MOD function returns the remainder after a number is divided by a divisor. It takes two arguments and its syntax is MOD(number, divisor).

The INT function rounds a number down to the nearest integer. It takes only one argument and its syntax is INT(number).

We use the following steps in this method:

  1. Select cell C2 and type in the formula:
=DATE(INT(B2/10^3),1,MOD(B2,INT(B2/10^3)))
  1. Press the Enter key and drag down the Fill Handle to copy the formula down the column:

The Julian dates in Column B are converted to standard Calendar dates in Column C.

Explanation of the formula

=DATE(INT(B2/10^3),1,MOD(B2,INT(B2/10^3)))
  • INT(B2/10^3). To generate a value for the first argument of the DATE function, we divide the value 2020366 in Cell B2 by 1000 because we want to extract only the first four digits that represent the year. This results in 2020.366. The INT function rounds this decimal number down to the nearest integer and it becomes 2020
  • MOD(B2,INT(B2/10^3)). To generate a value for the third argument of the DATE function we plug in the number from the previous step into this part of the formula and it becomes MOD(2020366,2020). The MOD function then returns the remainder value of 366.
  • =DATE(INT(B2/10^3),1,MOD(B2,INT(B2/10^3))) becomes =DATE(2020,1,366) and the DATE function returns the Calendar date of 31/12/2020. Although the value 1 is the second argument, the DATE function uses the number of elapsed days in the year (366) to compute the correct month.

Method 3 – Use a User Defined Function

We can also use Excel VBA to create a User Defined Function that we can use to convert Julian dates to standard Calendar dates using the following steps:

  1. In the active worksheet press Alt + F11 to switch to the Visual Basic Editor. Alternatively, we can choose Developer >> Code >> Visual Basic on the Excel Ribbon:
  1. Right-click on the workbook in the Project Window and insert a new module:
  1. In the new module type in the following JUDtoCAD (Julian Date to Calendar Date) function procedure:
Function JUDtoCAD(JUD As String) As Long
    Dim Year As Integer
    Dim Day As Integer
    Dim CAD As Long
    Year = CInt(Left(JUD, 4))
    Day = CInt(Right(JUD, 3))
    CAD = DateSerial(Year, 1, Day)
    JUDtoCAD = CAD
End Function
  1. Save the module and save the workbook as a macro-enabled workbook.
  2. Press Alt + F11 to switch back to the active worksheet. Alternatively, we can click on the View Microsoft Excel button on the toolbar:
  1. Select cell C2 and type in the formula =JUDtoCAD(B2) as follows:
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:

The Julian dates in Column B are converted to standard Calendar dates in Column C.

Explanation of the JUDtoCAD User Defined Function

Function JUDtoCAD(JUD As String) As Long
    Dim Year As Integer
    Dim Day As Integer
    Dim CAD As Long
    Year = CInt(Left(JUD, 4))
    Day = CInt(Right(JUD, 3))
    CAD = DateSerial(Year, 1, Day)
    JUDtoCAD = CAD
End Function
  • Function JUDtoCAD(JUD As String) As Long.  The name of the function is JUDtoCAD and its data type is Long. It takes one argument of string data type.
  • Three variables are declared: Year of Integer data type, Day of Integer data type, and CAD of Long data type.
  • Year = CInt(Left(JUD, 4)). The Left VBA in-built function returns four characters from the start of the JUD text string that is passed to it. The in-built CInt function converts the four characters into an integer value and assigns it to the Year variable.
  • Day = CInt(Right(JUD, 3)). The Right VBA in-built function returns three characters from the end of the JUD text string that is passed to it. The in-built CInt function converts the three characters into an integer value and assigns it to the Day variable.
  • CAD = DateSerial(Year, 1, Day). The DateSerial function returns a date for the specified year, month, and day.
  • JUDtoCAD = CAD. The date value returned by the DateSerial function is assigned to the JUDtoCAD variable, and it is the date returned when we run the JUDtoCAD function.

Conclusion

In this tutorial, we explained that a Julian date format is the date format that uses the combination of the year and the number of elapsed days in the year.

The Julian dates are mostly used in manufacturing environments as timestamps and as batch reference numbers. Dates in this format however cannot be used for calculations involving dates in Excel hence the need to convert them into standard Calendar dates.

We looked at three methods that we can use to convert Julian dates into standard Calendar dates:  use the combination of DATE, LEFT, and RIGHT functions, use the combination of DATE, MOD, and INT functions, and employ the use of a User Defined Function.