Get Day Number of Year

HomeExcel FormulasGet Day Number of Year

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.

  1. Refer to cell A1 where you have the date.
  2. Now type the minus sign (-) and then enter the date function.
  3. In the date function, use the year function further and then refer to cell A1.
  4. In the second argument, enter 1, and in the last enter 0.
  5. 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.

calculate-day-number-of-date

Now let’s understand this formula.

formula-to-get-day-number

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.

first-part-of-day-number-formula

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 that 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.

sample-file.xlsx