Exceljet

Quick, clean, and to the point

Get nth day of year

Excel formula: Get nth day of year
Generic formula 
=date-DATE(YEAR(date),1,0)
Summary 

To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions. In the example shown, the formula in C5 is:

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

With the date "January 9, 2018" in cell B5, the formula returns 9, since January 9 is the 9th day of the year.

Explanation 

This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula:

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

The result is nth day of the year, based on the date in cell B5. Notice the day argument in the DATE function is supplied as zero. A nice feature of DATE is it can handle day values that are "out of range" and adjust the result appropriately. When we give DATE a year for year, a 1 for month, and a zero for day, the DATE function returns the last day of the previous year:

DATE(YEAR(B5),1,0) // last day of previous year

So, the formula is solved like this

=B5-DATE(YEAR(B5),1,0)
=B5-DATE(2018,1,0)
=43109-43100
=9

nth day this year

To adjust the formula to return the nth day of year for the current date, just use the TODAY function for the date:

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

The logic of the formula remains the same, but the date values are supplied by the TODAY function.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.