Usually, users have to perform many tasks in Excel, which includes mentioning dates. These dates can be days, months, or years of a particular time. Mentioning normal calendar dates or Gregorian dates is easier than mentioning Julian dates. Because there is no direct format or formula to mention Julian dates in Excel. So, in this article, I will show you how to convert a date to a Julian date in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

## Introduction to Julian Date in Excel

Firstly, I will explain to you what a Julian date in Excel is. In general, Julian dates are expressed as 5-digit numerals, the first two of which represent the year and the final three, the day of that particular year. For example, the normal or Gregorian date format is 01-03-2022, which indicates the first March of 2022. But, in Julian format, you will write the above date as 2260, where 22 represents the year 2022 and 60 represents the sixtieth day of that year.

## 3 Easy Ways to Convert Date to Julian Date in Excel

In this article, you will see three ways to convert a date to a Julian date in Excel. For this conversion, I will require multiple Excel functions. I will use **the TEXT function**, **the DATE function**, **the YEAR function**,** the RIGHT function**, and **the DATEVALUE function** to complete my task.

To illustrate my article further, I will use the following sample data set.

### 1. Combine TEXT, DATE, and YEAR Functions

In the first procedure, I will combine three Excel functions, which are the **TEXT**, **DATE**, and **YEAR** functions. By using these functions, I will convert a Gregorian date into a Julian one. Also, how each of the functions works will be described in the formula breakdown. Let’s go through the following steps for a better understanding.

**Steps:**

- First of all, to convert the Gregorian date of cell
**B5**into a Julian date use the following combination formula in cell**C5**.

`=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)`

**Formula Breakdown:**

**TEXT(B5,”yy”)&B5-DATE(YEAR(B5),1,0)**

**TEXT(B5,”yy”)**: Firstly,**the TEXT function**will show the year value in two digits from the reference cell. You will see these two digits in the first place after conversion.**TEXT(B5,”yy”)&B5-DATE(YEAR(B5),1,0)**: Secondly, the part after the ampersand (&) will show the number of days of that particular year from the first part and then the ampersand will join these two results to show the actual value.

- Secondly, press
**Enter**to see the Julian date which will be a four-digit numerical value. - Here, the first two digits represent the year and the last two digits represent the number of days of that particular year.
- Then, use
**AutoFill**to drag the formula to the lower cells of the column.

- Moreover, to convert dates into a five digits Julian date, modify the above formula like the following and insert it into cell
**C5**.

`=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")`

**Formula Breakdown:**

**TEXT(B5,”yy”)&TEXT(B5-DATE(YEAR(B5),1,0),”000″)**

**TEXT(B5,”yy”)**: First of all,**the TEXT function**will show the year value in two digits from the reference cell. You will see these two digits in the first place after conversion.**TEXT(B5-DATE(YEAR(B5),1,0),”000″)**: Secondly, the part after the ampersand will show the date value into the number of days and the result of this part will be in a three-digit format.**TEXT(B5,”yy”)&TEXT(B5-DATE(YEAR(B5),1,0),”000″)**: Finally, the ampersand (&) sign will join the above two parts to show the final result.

- Fourthly, press
**Enter**and then, drag the formula to the lower cells using**AutoFill**.

- Additionally, if you want to see a Gregorian date into a seven-digit Julian date, then in cell
**C5**apply the following formula.

`=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")`

**Formula Breakdown:**

**YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),”000″)**

**YEAR(B5)**: Firstly,**the YEAR function**will result in the year in full format from the value of cell**B5**.**YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),”000″)**: Secondly, the part after the ampersand will show the number of days of that year. Finally, using the ampersand sign, you will be able to join the above two parts.

- Finally, to see the desired result hit
**Enter**and then use**AutoFill**.

**Read More: ****How to Convert Text to Date in Excel (10 ways)**

**Similar Readings**

**How to Convert Timestamp to Date in Excel (7 Easy Ways)****Convert Serial Number to Date in Excel (7 Easy Ways)****How to Convert Week Number to Date in Excel (2 Suitable Methods)****Convert Text to Date and Time in Excel (5 Methods)****How to Convert SAP Timestamp to Date in Excel (4 Ways)**

### 2. Merge RIGHT, YEAR, and DATE Functions

In the second method of this article, you will see the merging of the **RIGHT**, **YEAR**, and **DATE** functions to convert a date to Julian’s date in Excel. Here, after giving input of normal or Gregorian date as a cell reference into the formula, the above functions will convert it into a Julian one. Follow the below-given steps for applying the formula.

**Steps:**

- Firstly, type the following formula in cell
**C5**, where you will see the merging of the above three functions to convert the Gregorian date in cell**B5**.

`=RIGHT(YEAR(B5),2)& B5-DATE(YEAR(B5),1,0)`

**Formula Breakdown:**

**RIGHT(YEAR(B5),2)&B5-DATE(YEAR(B5),1,0)**

**RIGHT(YEAR(B5),2)**: Firstly,**the RIGHT function**will show the right two digits of the year from cell**B5**.**RIGHT(YEAR(B5),2)&B5-DATE(YEAR(B5),1,0)**: Secondly, the part after the ampersand operator will result in the number of days of the year from cell**B5**that matches with the day and month value of that same cell. Finally, the operator will join these two parts.

- Secondly, hit the
**Enter**button to see the desired result. - Consequently, to see all the conversions in the lower cells use the
**Fill Handle**.

**Read More: ****How to Convert General Format to Date in Excel (7 Methods)**

### 3. Insert DATEVALUE Function into TEXT Function

This is the final method of this article. Here, I will use only two functions to achieve my result. I will use a formula with **the TEXT function**, then in the formula, I will insert **the DATEVALUE function** of Excel. The detailed steps of this procedure are as follows.

**Steps:**

- In the beginning, insert the following formula that consists of both
**the TEXT function**and**the DATEVALUE function**in cell**C5**.

`=TEXT(B5,"yy")&TEXT((B5-DATEVALUE("1/1/"&TEXT(B5,"yy"))+1),"000")`

**Formula Breakdown:**

**TEXT(B5,”yy”)&TEXT((B5-DATEVALUE(“1/1/”&TEXT(B5,”yy”))+1),”000″)**

**TEXT(B5,”yy”)**: Firstly,**the TEXT function**will show the year value in two digits from the reference cell. You will see these two digits in the first place after conversion.**TEXT((B5-DATEVALUE(“1/1/”&TEXT(B5,”yy”))+1),”000″)**: Secondly, this part of the formula will give the number of days that matches with the days and months from the value of cell**B5**as a result. Finally, the ampersand operator will join both of the parts two show the desired result.

- Finally, after pressing
**Enter**, the formula will convert the date in cell**B5**into a Julian date. - Furthermore, drag the
**Fill Handle**to use the above formula in the lower cells.

**Read More: How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)**

## Things to Remember

- While writing or inserting the formula, remember to give proper cell reference, otherwise, you won’t get the desired values.
- Use the ampersand (&) sign and other symbols in the formula properly. If you misplace or miswrite any of the symbols then the formula will not show actual results.

## Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to convert a date to a Julian date in Excel. Please share any further queries or recommendations with us in the comments section below.

The** ExcelDemy** team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

## Related Articles

**How to Convert Number to Date in Excel (6 Easy Ways)****Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)****Text Won’t Convert to Date in Excel (4 Problems & Solutions)****How to Convert Unix Timestamp to Date in Excel (3 Methods)****Convert Active Directory Timestamp to Date in Excel (4 Methods)****How to Disable Auto Convert to Date in Excel (2 Methods)****Excel VBA to Convert Date and Time to Date Only**