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)
Explanation 

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:

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

With the date "June 1, 2016" in cell B4, the formula returns 153, since June 1st is the 153rd day of that year.

How the formula works

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 B4. The result is nth day of the year.

Note the day argument in DATE is supplied as zero. A nice feature of DATE function is it can handle DAY values that are "out of range" and adjust the result appropriately. When we give DATE a year, a 1 for month, and zero for DAY, the DATE function returns the last day of the previous year.

Day of year as of today

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)
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.