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.
