# How to Convert Gregorian to Julian Date in Excel – 3 Easy Methods

## 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.

### 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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.

1. I have “nadee20240604521”
Now I Julian date is 2024060 now can you please convert Julian to gregorian date from above string.

Lutfor Rahman Shimanto Mar 7, 2024 at 6:04 PM

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

Advanced Excel Exercises with Solutions PDF