How to Convert Date to Julian Date in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Convert Date to Julian Date in Excel: 3 Easy Ways

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 TEXT, DATE, YEAR, RIGHT, and DATEVALUE functions to complete my task.

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

3 Easy Ways to Convert Date to Julian Date in Excel


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)

Combine TEXT, DATE, and YEAR Functions to Convert Date to Julian Date in Excel

Formula Breakdown:

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

  • TEXT(B5,”yy”): 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): 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.
  • 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.

  • To convert dates into a five-digit 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”): 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″): The part after the ampersand will show the date value in 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″): The ampersand (&) sign will join the above two parts to show the final result.
  • Press Enter and then, drag the formula to the lower cells using AutoFill.

  • 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): 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″): The part after the ampersand will show the number of days of that year. Using the ampersand sign, you will be able to join the above two parts.
  • To see the desired result hit Enter and then use AutoFill.

Read More: How to Use Formula to Change Date Format in Excel


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)

Merge RIGHT, YEAR, and DATE Functions to Convert Date to Julian Date in Excel

Formula Breakdown:

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

  • RIGHT(YEAR(B5),2): 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): 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.
  • Hit the Enter button to see the desired result.
  • Use the Fill Handle to see all the conversions in the lower cells.

Read More: How to Convert Date to Text YYYYMMDD


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. The detailed steps of this procedure are as follows.

Steps:

  • In the beginning, insert the following formula that consists of both TEXT and DATEVALUE functions in cell C5.
=TEXT(B5,"yy")&TEXT((B5-DATEVALUE("1/1/"&TEXT(B5,"yy"))+1),"000")

Insert DATEVALUE Function into TEXT Function to Convert Date to Julian Date in Excel

Formula Breakdown:

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

  • TEXT(B5,”yy”): 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″): 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 to show the desired result.
  • Finally, after pressing Enter, the formula will convert the date in cell B5 into a Julian date.
  • Drag the Fill Handle to use the above formula in the lower cells.

Read More: Convert 7-Digit Julian Date to Calendar Date in Excel


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.

Download Practice Workbook

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


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


<< Go Back to Change Date Format | Date Format | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo