If you need to know the day number of a specific date in a year, Excel has a built-in function to make it easy. By using the DATE and YEAR functions, you can determine the day number of any date.
Guide How to Get Day Number of Year in Excel
First, enter the date you want to determine the day number for in any cell of the worksheet.
Next, enter the following formula in another cell.
=A1-DATE(YEAR(A1),1,0)In the formula, replace “A1” with the cell reference that contains the date you want to determine the day number for.
Press “Enter” to execute the formula.
The result will be the day number of the date in the year.
The formula calculates the difference between the date in cell A1 and the date of the previous year’s December 31st. This difference represents the number of days from the start of the year to the date in cell A1.
How to Get Day Number of Year in Excel Example
For example, if you enter the date “14/03/2023” in cell A1, then add following formula in cell B2.
=A1-DATE(YEAR(A1),1,0)
It will return “105” as you can see which is the day number of that date in the year.
- YEAR(A1) = 2023
- DATE(YEAR(A1),1,0) = 31/12/2022
- A1-DATE(YEAR(A1),1,0) = 105
Here is a breakdown of the formula
- YEAR(A1): This function extracts the year value from the date in cell A1.
- DATE(YEAR(A1),1,0): This function creates a date value that represents December 31st of the previous year by using the extracted year value from cell A1 and specifying the month and day as 1 and 0, respectively.
- A1-DATE(YEAR(A1),1,0): This calculates the difference between the date in cell A1 and the date of the previous year’s December 31st.
Things to Keep in Mind
- The DAY function returns the day number of a date, ranging from 1 to 31.
- The DATE function creates a date from the year, month, and day values specified in its arguments.
- The YEAR function returns the year value from a date.
- The MONTH function returns the month value from a date.
- When using cell references in the formula, be sure to use the correct cell reference for the date you want to determine the day number for.