Convert date to Julian format

How to Convert date to Julian Format?

If you want to convert a date to Julian Format, you can do that by using YEAR(),DATE() and TEXT() functions.

What is Julian Format?

Julian Format merges year value of date with the ordinal day for that year.

There are a different version of this format. The year could be four digits or two digits. Similarly, day number can be zero-padded to either two or three places.


=YEAR(date) & TEXT(date - DATE(YEAR(date),1,0), “000”)

Let us go through an example to understand the working of this formula.


How to use the Formula to Convert date to Julian format?

In this example, you have a small table containing different dates. To convert into Julian format with a four-digit year and day number padded with zeros to three places:

  1. Select any column, where you want to display Julian format for any particular date. In this case, it is E6.
  2. In the function box, type YEAR(C6)&TEXT(C6-DATE(YEAR(C6),1,0),”000″).
  3. This formula works in two parts.
  4. Part one contains YEAR(C6), which will return the year of the given date.
  5. The second part of the formula deals with returning the day of the year.
  6. We use DATE function to return the last date of the prior year. In this example, it would be 12-31-1998.
  7. The current date received from DATE() function is subtracted from current date i.e.6-Jan-99.
  8. This subtraction will give us the day of the year.
  9. We can use TEXT() function to format it and append it to year received from YEAR(C6) using & operator.  

Figure 1. Example 1 of how to convert a date to Julian Format


Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment