Excel Formula: Convert Date to Julian

Sometimes, you need to convert the standard date to the Julian date in Excel, here this article introduces a formula which combines the TEXT, YEAR and DATE functions to solve this job.

If you want to follow along with this tutorial, please download the example spreadsheet.

Generic formula:

TEXT(date,"yy")&date-(DATE(YEAR(date),1,0))

Arguments

Date: the date you want to convert to Julian date.

Return Value

This formula returns a Julian date in a text(yyd) format. First two digits represent the two-digits year, the else numbers represent the number of days.

How this formula work

A list of dates are in cell B3:B6, to convert them to Julian date, please use below formula：

=TEXT(B3,"yy")&B3-(DATE(YEAR(B3),1,0))

Press Enter key, then drag autofill handle down to apply this formula.

Explanation

TEXT function converts a number to a text in a specific format. As we know, the date is stored as a number in Excel, here the TEXT function converts the date to text in the yy format.

YEAR function gets the year from a date.

DATE function combines year, month, and day numbers from separate cells to a valid date. Here the formula

• DATE(YEAR(B3),1,0)
• =DATE(2020,1,0)
• =12/31/2019

Tip: if the day number is less than 1, it will subtract absolute number of days from the first day of the month.

• B3-(DATE(YEAR(B3),1,0))
• =3/12/2020-12/31/2019
• =72

Note

If you want to get the Julian date in 4-digit year and 3-digit day number, please use below formula

=TEXT(B3,"yyyy")&TEXT(B3-DATE(YEAR(B3),1,0),"000")

---

Relative Formulas

• Calculate Difference Between Two Dates
  Here in this tutorial, it introduces the formulas on calculating difference between two dates in years, months, weeks, and days.
• Convert date string to datetime
  Sometimes, while you import or paste some datetime data from other devices to Excel, the datetimes maybe not be recognized as proper dates...
• Calculate Days Remaining Between Two Dates
  Here, this tutorial provides a formula to quickly calculate the left days between two dates.
• Calculate Hours Minutes Seconds Between Two Times
  Here in this tutorial, it introduces some formulas to quickly present the time difference result as single time unit.

Relative Functions

• Excel IF Function
  Test for specified conditions, then returns the corresponding values
• Excel VALUE Function
  Convert text to number.
• Excel MONTH Function
  The MONTH is used to get the month as integer number (1 to 12) from date.
• Excel DAY Function
  DAY function gets the day as a number (1 to 31) from a date
• Excel YEAR Function
  The YEAR function returns the year based on the given date in a 4-digit serial number format.