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.
Related formulas
Get percent of year complete
Convert date to Julian format
Related functions
DATE Function
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
YEAR Function
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.
