Get nth day of year
Generic formula
=date-DATE(YEAR(date),1,0)
Related formulas
Convert date to Julian format
Explanation
If you want to get the day of year from a date (i.e. the nth day of year), you can use a formula that uses the DATE and YEAR functions together.
For example, with the date "June 1st, 2016" in cell B4, the following formula will return 153:
=B4-DATE(YEAR(B4),1,0)
That is, June 1st, 2016 is the 153th 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 result 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 that 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)