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.

Tutorial Content

## 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:

- Use the combination of
**DATE, LEFT, and RIGHT functions**. - Use the combination of
**DATE, MOD, and INT functions**. - 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:

- Select
**Cell C2**and type in the formula:

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

- 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**

1 |
=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:

- Select cell
**C2**and type in the formula:

1 |
=DATE(INT(B2/10^3),1,MOD(B2,INT(B2/10^3))) |

- 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**

1 |
=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:

- 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**:

- Right-click on the workbook in the
**Project Window**and insert a new module:

- In the new module type in the following
**JUDtoCAD**(Julian Date to Calendar Date)**function procedure:**

1 2 3 4 5 6 7 8 9 |
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 |

- Save the module and save the workbook as a
**macro-enabled workbook**. - Press
**Alt + F11**to switch back to the active worksheet. Alternatively, we can click on the**View Microsoft Excel**button on the toolbar:

- Select cell
**C2**and type in the formula =JUDtoCAD(B2) as follows:

- 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**

1 2 3 4 5 6 7 8 9 10 |
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**.