How to Convert Gregorian to Julian Date in Excel

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

3 Easy Ways to Convert Date to Julian Date in Excel

Method 1 – Combining the TEXT, DATE, and YEAR Functions

Steps:

Enter the formula in 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”): 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

Steps:

Enter the following formula in C5:

=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): 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

Steps:

Enter the following formula in 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”): 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.

Download Practice Workbook

Download the free Excel workbook here.

Convert Date to Julian Date.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Nadee Mar 2, 2024 at 5:53 PM

I have “nadee20240604521”
Now I Julian date is 2024060 now can you please convert Julian to gregorian date from above string.
- Reply
  Lutfor Rahman Shimanto Mar 7, 2024 at 6:04 PM
  
  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