With the date “January 9, 2018” in cell B5, the formula returns 9, since January 9 is the 9th day of the year. 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: So, the formula is solved like this
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: The logic of the formula remains the same, but the date values are supplied by the TODAY function.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
