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.
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”): 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)
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")
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
- How to Convert Date to Text Month in Excel
- How to Remove Year from Date in Excel
- Stop Excel from Converting Date to Number in Formula
<< Go Back to Change Date Format | Date Format | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I have “nadee20240604521”
Now I Julian date is 2024060 now can you please convert Julian to gregorian date from above string.
Dear NADEE
Thanks for sharing your problem with us. I understand that you want to extract the Julian date from a string and convert the Julian date to the Gregorian Date.
This can be accomplished using a simple formula. To demonstrate an example, I have taken the following dataset. Here, column A contains the employee names, and column B contains the string code with Julian dates. In column C, I have applied the following formula to extract the Julian Date:
=MID(B2,LEN(A2)+1,7)Afterward, I applied the following formula to convert the Julian date to the Gregorian date:
=DATE(LEFT(C2,4),1,0)+MOD(C2,1000)Thus, you can easily convert a Julian date to a Gregorian date. I hope this solution will be helpful for your requirements. Let us know your feedback.
Regards
Seemanto Saha
Team ExcelDemy