Md. Araf Bin Jayed
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.
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
