Spreadsheet Practice

How to convert a date to a Julian date (YYDDD) in Excel

Formula (Excel and Google Sheets)

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

Join the two-digit year and the three-digit day of the year: =TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000").

Result on the example
one result per row in B2, filled down to B6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

TEXT(A2,"yy") gives the two-digit year, and A2 minus December 31 of the previous year gives the day of the year. TEXT(…,"000") pads that to three digits, so March 5, 2026 becomes 26064.

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

Result"26001"

  1. A21/1/2026

    The value in A2.

  2. TEXT(A2,"yy")"26"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueA21/1/2026
    • format_text"yy"
  3. YEAR(A2)2026

    YEAR: Year of a date.

    • serial_numberA21/1/2026
  4. DATE(YEAR(A2),1,0)12/31/2025 (46022)

    DATE: Builds a date from parts.

    • yearYEAR(A2)2026
    • month1
    • day0
  5. A2-DATE(YEAR(A2),1,0)1

    The - operator subtracts.

  6. TEXT(A2-DATE(YEAR(A2),1,0),"000")"001"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueA2-DATE(YEAR(A2),1,0)1
    • format_text"000"
  7. TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")"26001"

    The & operator joins as text.

In Google Sheets

The same formula works in Google Sheets: =TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=RIGHT(YEAR(A2),2)&TEXT(A2-DATE(YEAR(A2),1,0),"000")26001, 26064, 26365…The same, taking the last two digits of the year.
=YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000")2026001, 2026064, 2026365…Different resultSeven-digit YYYYDDD, which avoids any doubt about the century. Four-digit year.

Common mistakes

  • The result is text. Converting it to a number drops the leading zero for 2000 to 2009 (05001 becomes 5001).
  • Julian date here means the year-and-day format used on food labels and in manufacturing, not the astronomical Julian Day Number.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Convert a date to a Julian date (YYDDD)

In B2, convert the date to a five-character Julian date (two-digit year, three-digit day of the year), and fill down to B6.

Questions

How do I convert it back to a date?

=DATE(2000+INT(B2/1000),1,MOD(B2,1000)).

What about the JD Edwards CYYDDD format?

It adds a century digit: =(YEAR(A2)-1900)*1000+A2-DATE(YEAR(A2),1,0) gives 126064 for March 5, 2026.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.