There are 365 or 366 (leap year) days in a year. And in this tutorial, you will learn to get the day number in the year for a date. That means which is the number of days out of 365 or 366 days.
Calculate Day Number for a Date
To write a formula to get a day number use the below steps.
- Refer to cell A1 where you have the date.
- Now type the minus sign (-) and then enter the date function.
- In the date function, use the year function further and then refer to cell A1.
- In the second argument, enter 1, and in the last enter 0.
- Enter the closing parentheses and hit enter.
As you can see, we have 2-Apr-2022 in cell A1, and the formula that we have used returned 92 in the result.
Now let’s understand this formula.
In the first part, we have the date function that you have used returns the last date of the previous year. So, as we have the date from the year 2022, the date function returns 31-Dec-2021.
When you subtract 31-Dec-2021 from the 02-Apr-2022 you get 92 in the result which is the day number of 02-Apr-2022 in the year 2022.
There’s one point that you need to remember Excel stores a date as a number. When you enter 1 in a cell and format it as a date Excel shows it as 01-Jan-1990.
Now the number for the date 02-Apr-2022 is 44653 and for 31-Dec-2021 is 44561, and the difference between both the numbers is 92.
- Add Month to a Date
- Add Years to Date
- Add-Subtract Week from a Date
- Calculate Age using Date of Birth
- Compare Two Dates
- Convert Date to Number
- Count Years Between Two Dates
- Create a Date Range
- DATEDIF Function in Excel
- Get Month from a Date
- Get Quarter from a Date [Fiscal + Calendar]
- Get Years of Service
- Get Day Name from a Date in Excel
- Get End of the Month Date in Excel
- Get Total Days in Month in Excel
- Highlight Dates Between Two Dates
- Quickly Concatenate Two Dates
- Random Date Generator (Excel Formula)
- 5 Custom Date Formats I Always Use and You Should Too
- Convert Text to Date
- Get First Day (Beginning) of the Month