Introduction to Julian Date in Excel
Julian dates are expressed as 5-digit numerals, the first two representing the year and the final three, the day.
Gregorian date format 01-03-2022 becomes 2260 in Julian format: 22 represents the year (2022) and 60 represents the sixtieth day of that year.
This is the sample data set.
Method 1 – Combining the TEXT, DATE, and YEAR Functions
Combine the TEXT, DATE, and YEAR functions.
Steps:
- Enter the formula in C5.
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Formula Breakdown:
TEXT(B5,”yy”)&B5-DATE(YEAR(B5),1,0)
- TEXT(B5,”yy”): shows the year value in two digits from the reference cell.
- &B5-DATE(YEAR(B5),1,0): shows the number of days of that particular year. The ampersand joins the two results.
- Press Enter to see the Julian date: a four-digit value (the first two digits represent the year and the last two digits represent the number of days of that particular year).
- Use the AutoFill to drag the formula to the rest of the cells.
- To convert the output into a five-digit Julian date, modify the above formula:
=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”): shows the year value in two digits from the reference cell.
- TEXT(B5-DATE(YEAR(B5),1,0),”000″): shows the date value in the number of days: a three-digit format.
- TEXT(B5,”yy”)&TEXT(B5-DATE(YEAR(B5),1,0),”000″): The ampersand (&) joins the two parts to return the final result.
- Press Enter.
- Drag the formula to the rest of the cells, using the AutoFill.
- To see a seven-digit Julian date, use 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): returns the year in full format from the value of B5.
- &TEXT(B5-DATE(YEAR(B5),1,0),”000″): shows the number of days of that year. The ampersand joins the two parts.
- Press Enter and use the AutoFill.
Read More: How to Use Formula to Change Date Format in Excel
Method 2 – Merging the RIGHT, YEAR, and DATE Functions
Use the RIGHT, YEAR, and DATE functions.
Steps:
- Enter the following formula in C5:
=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): Â shows the two digits of the year on the right, in B5.
- &B5-DATE(YEAR(B5),1,0): returns the number of days that match the days and months in B5. The Ampersand joins the two parts.
- Press Enter.
- Use the Fill Handle to drag the formula to the rest of the cells.
Method 3 – Applying the DATEVALUE Function in the TEXT Function
Use the DATEVALUE function in the Text function.
Steps:
- Enter the following formula in 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”): shows the year value in two digits from the reference cell.
- TEXT((B5-DATEVALUE(“1/1/”&TEXT(B5,”yy”))+1),”000″): returns the number of days that match the days and months in B5. The ampersand joins the two parts.
- Press Enter.
- Use the Fill Handle to drag the formula to the rest of the cells.
Read More: Convert 7-Digit Julian Date to Calendar Date in Excel
Download Practice Workbook
Download the free Excel workbook here.
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 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