To convert a date into a Julian date — the yyyyddd format where the last three digits are the day of the year — combine the year with the day number: =TEXT(A1,"yyyy")&TEXT(A1-DATE(YEAR(A1),1,0),"000"). So 20-Nov-2026 becomes 2026324.
The DATE(YEAR(A1),1,0) part builds 31 December of the previous year, and subtracting it gives the day count; the “000” format pads it to three digits so day 5 reads as 005. For the two-digit year variant, swap the first part to TEXT(A1,"yy"), giving 26324.
To convert back, feed the pieces to DATE: =DATE(LEFT(A2,4),1,RIGHT(A2,3)) — the day number can exceed 31 and DATE rolls it into the right month automatically.
Two things worth knowing: the result is text, not a date, so it can’t be sorted chronologically or used in date arithmetic, and this format is more precisely called an ordinal date — the astronomical Julian Day Number is a different thing entirely, counting days since 4713 BC.
If you want to convert a regular date into a Julian date in Excel, you need to get the year and the day number from the date. And then combine both where year comes first and day number comes second. Below is the format of a Julian date.
Apart from this, you can use the below format as well. There are multiple formats for Julian’s date that you can use.
Excel Formula for Julian Date
Below are the steps to write this formula:
- First, enter the TEXT function in a cell to get the year from the date TEXT(A1,”YYYY”).
- After that, enter an ampersand (&) to enter the second formula to get the day of the year.
- Now here, you again need to use the TEXT along with the DATE and YEAR.
- Therefore, enter this formula TEXT(A1-DATE(YEAR(A1),1,0),”000″) after that ampersand to get the day of the year.
The moment you hit the enter key to get it returns a number, a Julian date. And in this date, the first four digits are the year, and the following three numbers are the day number of the year.
=TEXT(A1,"YYYY")&TEXT(A1-DATE(YEAR(A1),1,0),"000")To understand this formula, you need to split it into two parts:
In the first part, you used the TEXT function to get the year from the date in the “YYYY” format.
And in the second part, we combine three functions, TEXT, DATE, and YEAR. YEAR returns the year from the original date; with that, DATE creates the 1st day of the year.
After that, TEXT uses that date and subtracts it from the original date. And in the end, using the format “000” converts it into a three-digit number, the year’s day number.
And in the whole, this formula gives you the year and day number in a single cell as a Julian date.
Julian Date in Other Formats
As I said at starting, there are multiple formats of the Julian date. And you can use any of these. For example, with the below formula, you can create a Julian date where you have the year as two digits and then the day as a three-digit number.
=TEXT(A1,"YY")&TEXT(A1-DATE(YEAR(A1),1,0),"000")Converting Back from Julian’s Date
If you already have a Julian date and want to convert it back to a regular date, you can use the below formula:
=DATE(LEFT(A2,4),1,RIGHT(A2,3))To understand this formula, you need to understand the DATE function because the primary function is the DATE function.
LEFT returns the year from the date by extracting four digits from the left. For example, in the second argument (month), you have 1, which means the first month (Jan).
Now, in the third argument, we have RIGHT, which returns the day number of the year. The day number here is 324, more than the total number of days a month usually has (30/31). And the DATE function is smart enough to adjust these days as months and give a correct date.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.