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 the 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.
=A1-DATE(YEAR(A1),1,0)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 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 numbers is 92.