With the date "January 9, 2018" in cell B5, the formula returns 9, since January 9 is the 9th day of the year.
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:
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:
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.
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...
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.