How to convert Julian’s date into a Calendar date in Excel

The Julian date, also known as the Julian day number, is a system of counting days that is widely used in astronomy, geology, and other scientific disciplines. It was introduced by Julius Caesar in 45 BCE as part of the Julian calendar reform. 

In this system, each day is assigned a unique number, with consecutive integers representing successive days. The Julian date does not take into account leap years or months, and it is a simple and uniform method for calculating the number of days between two dates. The Julian date consists of a 7-digit number in which the first 4 digits are years and the last 3 digits are the number of days passed in that year.

Let’s consider a scenario where you are tracking the growth of a plant in a greenhouse and recording the various measurements of the plant’s height, leaf count, and flower count over a time using Julian dates. In this tutorial, we will learn how to convert Julian’s dates into Calendar dates.

Following are some simple methods by which we can convert Julian’s Dates to Calendar dates:

Method 1 – By using DATE, LEFT, and RIGHT Functions

Understanding the Functions:

• DATE Function:

The DATE function in Excel is used to create a date by specifying the year, month, and day values. It returns a serial number that represents the date in Excel’s date format. The syntax for the DATE function is as follows:

=DATE(year, month, day)

The “year” argument is a four-digit value or a cell reference that contains the year.

The “month” argument is a numeric value or a cell reference that contains the month (1 for January, 2 for February, and so on).

The “day” argument is a numeric value or a cell reference that contains the day of the month.

LEFT Function:

The LEFT function in Excel enables you to extract characters from the leftmost side of a text string.

The syntax for the LEFT function is: 

=LEFT(text, num_chars)

The “text” parameter refers to the text string you want to extract characters from.

The “num_chars” parameter indicates the number of characters you want to obtain from the left side of the text string.

RIGHT Function:

In Excel, the RIGHT function enables you to extract a designated number of characters from the right side of a text string.

The syntax for the RIGHT function is as follows: 

=RIGHT(text, num_chars)

The “text” parameter represents the text string you wish to extract characters from.

The “num_chars” parameter indicates the number of characters you want to retrieve from the right side of the text.

Step 1 – Selection of the Cell

• Select any vacant cell in which you want to convert the Julian date to the Calendar date. For example, we have selected cell B2. 
• In this cell, we will apply DATE, LEFT, and RIGHT Functions.

Step 2 – Writing and implementing the formula

• After selecting the cell, press the = button on your keyboard.
• Then, type DATE and select the DATE Function from the list by pressing the tab button.
• Now, write LEFT and select the LEFT Function by the same button as mentioned above.
• Once you’ve done that, select the cell in which Julian’s date is present which is cell A2 in our case.
• After that, press comma (,) and write “4” (without quotes) which will extract 4 digits from the left which is a year.
• Add a closing parenthesis and again write comma (,) and you’ll move to the next parameter which is the month.
• Simply write “1” (without quotes) in this parameter.
• Now press the comma button (,) again and type RIGHT and select RIGHT Function from the list by tab button.
• Once again, select the cell in which Julian’s Date is present. For example, it is cell A2 in our case. 
• After typing a comma (,) input the number “3” (without quotes) to obtain the three characters located at the end from Julian Date.
• Then, add closing parenthesis 2 times.
• Your formula would look like this after following the above steps:

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

• Then press Enter and the Julian date would be converted into the Calendar date.

Step 3 – Implementing the formula to the whole range

• To apply the formula across a range of cells, begin by selecting the cell containing the desired result, such as cell B2. 
• Next, navigate to the bottom right corner of the cell until your cursor transforms into a plus (+) shape, known as the fill handle.
• Double-Click on this fill handle and the formula would be applied to the whole range.

Method 2 – By using the VBA code

Step 1 – Selecting the cell

• Choose an empty cell where you wish to convert the Julian date to the Calendar date. For example, we have selected cell B2.
• In this cell, we will apply the function created by us using the VBA code.

Step 2 – Adding a module

• For adding a module, navigate to the Developer tab.
• After that, click on the first option named Visual Basic.
• You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
• Then, click on the Insert tab in this window and click on the Module option.
• Now, a new module would open.

Step 3 – Writing the code

• After you’ve opened the module, copy and paste the following code:

Function Conv_JLD_CD(JLD As String) As Date     Dim YearP As String   ‘ Variable to store the year portion of JLD     Dim DayP As String    ‘ Variable to store the day portion of JLD     Dim Cal_Dt As Date    ‘ Variable to store the converted date         YearP = Left(JLD, 4)  ‘ Extract the leftmost 4 characters as the year part     DayP = Right(JLD, 3)  ‘ Extract the rightmost 3 characters as the day part         ‘ Generate the converted date using the YearP and DayP variables     Cal_Dt = DateSerial(CInt(YearP), 1, CInt(DayP))         ‘ Return the converted date     Conv_JLD_CD = Cal_Dt End Function

Step 4 – Using the created Function

• After writing the code, press (Ctrl+S) to save it.
• Then, close the window and select the cell in which you want to convert the Julian Date into Calendar Date which is cell B2 in our case.
• Then press the = button on your keyboard.
• After doing that, type Conv_JLD_CD and select the Conv_JLD_CD Function by pressing the tab button.
• Now, select the cell in which Julian’s Date is present. For example, cell A2 in the given case.
• Once you’ve followed all the aforementioned steps, press Enter and you’ll get the result in Calendar Date.

Step 5 – Implementing the formula to the whole range

• To apply the formula across a range of cells, begin by selecting the cell containing the desired result, such as cell B2. 
• Next, navigate to the bottom right corner of the cell until your cursor transforms into a plus (+) shape, known as the fill handle.
• Double-Click on this fill handle and the formula would be applied to the whole range.