Let’s say you are a project manager working on a large construction project. The project has multiple phases and milestones that must be completed by specific dates throughout the year. For this, you need to track the day of the year for each milestone date, which allows for a standardized way of reporting.
In Excel, you can use a formula to get a day of the year from the date. As you know, there are 365 or 366 (leap year) days in a year; we need to use a formula to get that, which is the day’s number in these 365 or 366 days.
And in this tutorial, you will learn to get the day number in the year for a date.
Calculate the Day Number for a Date
Here we need to use a combination of DATE and YEAR functions. DATE helps you create a specific date. You need to tell it the year, month, and day you want. And YEAR gives you the year from any date you specify in it.
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.
Make sure to have valid dates as per Excel’s date system. You can check this by changing the cell format to “General” and seeing if it converts to a serial number.
Now let’s understand this formula
To understand this formula, you need to split it into two parts.
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.
And if you want to take the current date as the date to get the day number for, you can use the TODAY function in the formula:
=TODAY()-DATE(YEAR(TODAY()),1,0)
Your formula should correctly handle leap years. The best part is, DATE automatically consider leap years when calculating days.
Calculate Day of Year using EDATE + DATE + YEAR
This formula counts the number of days from the start of the year to your specific date. To write this formula, you need to understand the EDATE, one of the most powerful functions in Excel.
EDATE – It helps you find a date that is a specific number of months before or after a given date.
=A1-EDATE(DATE(YEAR(A1),1,0),1)+1
First, it finds the last day of the previous year and subtracts this from your date in A1. Adding 1 at the end adjusts the count so that January 1st is day 1 rather than day 0.
Using DATEDIF Function for Day of Year
DATEDIF calculates the difference between two dates. You can specify the start date, end date, and whether you want the difference in days, months, or years.
And below is the formula that you can use to get the day number.
=DATEDIF(DATE(YEAR(A1),1,1),A1,"d")+1
It starts by finding the first day of the year for the date in cell A1 (01-May-24), then calculates how many days have passed from this starting point to the date in A1.
Adding 1 at the end of the formula ensures that January 1st is counted as day 1, not day 0.
Custom Function for Day Number of Year
If you like to use VBA, you can write a code for a custom function.
Function DayOfYear(d As Date) As Integer
DayOfYear = d - DateSerial(Year(d), 1, 0)
End Function
After adding this to your VBA editor, you can use the formula =DayOfYear(A1), where A1 is your date.