The Julian date format is generally used as a timestamp in programming, manufacturing or other related fields like a reference for a batch number. Generally, it has two format variations, comprising of 5 or 7 digits numbers, but in most of the cases, 5 digits format is used. Here, we will learn how to convert a date to Julian format in both of these formats.

*Figure 1. Converting a Date to Julian Format*

**Converting a Date to 5 Digits Julian Format**

Mostly 5 digits number of Julian formats is used where the first 2 digits represent the year and the last 3 digits represent the day of the year. In Excel, to convert a date to Julian format with 5 digits number, we need to use the following formula in cell C2 as per our example;

**=RIGHT(YEAR(B2),2)&TEXT(B2-DATE(YEAR(B2),1,0),"000")**

The first part of the formula,

, extracts the first 2 digits representing the year, and the second part of the formula, **RIGHT(YEAR(B2),2)**

, returns the last 3 digits number representing the day of the year from a given date. Copy or drag down the fill handle to convert remaining dates to 5 digits Julian format.**TEXT(B2-DATE(YEAR(B2),1,0), "000")**

* Figure 2. Converting a Date to 5 Digits Julian Format*

**Converting a Date to 7 Digits Julian Format**

In 7 digits Julian format, the first 4 digits represent the year and the last 3 digits represent the day of the year. In our example, to covert a date to Julian format with 7 digits number, we need to use the following formula in cell D2 and copy the formula to other dates;

**=YEAR(B2)&TEXT(B2-DATE(YEAR(B2),1,0),"000")**

The first part of the formula, ** YEAR(B2), **returns the 4 digits indicating the year and last part of the formula,

**TEXT(B2-DATE(YEAR(B2),1,0), "000")**

, returns the last 3 digits of the 7 digits Julian format.*Figure 3. Converting a Date to 7 Digits Julian Format*

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment