How to Find the Day of the Year in Excel

Number dates 1–365 for crop planning, count days left in the year, and decode YYDDD Julian lot codes into dates.

You will learn to

• Return the day number of a date within its year
• Count the days left in the year
• Convert a YYDDD Julian code into a date

How it works

The day of the year is the date minus “day 0” of its year: =B2-DATE(YEAR(B2),1,0). DATE with day 0 returns December 31 of the previous year, so January 1 gives 1 and December 31 gives 365 (366 in leap years). Format the result as a number, not a date.

The reverse is handy for food and manufacturing lot codes written as YYDDD (Julian dates): =DATE(2000+INT(H1/1000),1,MOD(H1,1000)) turns 26245 into September 2, 2026, because DATE rolls day 245 of January forward into the right month.

=B2-DATE(YEAR(B2),1,0)