Day of the Year in Excel
An easy formula that returns the day of the year for a given date. There's no built-in function in Excel that can do this.
1. To get the year of a date, use the YEAR function.
2. Use the DATE function below to return January 1, 2024. The DATE function accepts three arguments: year, month and day.
3. The formula below returns the day of the year.
Explanation: dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. June 23, 2024 is the same as 45466. January 1, 2024 is the same as 45292. Subtracting these numbers and adding 1 gives the day of the year. 45466 - 45292 + 1 = 174 + 1 = 175.
4. 2024 is a leap year. A leap year has 366 days. December 31st is the last day of the year. We can check this.
5. Bonus. The formula below calculates the number of days in a year.
Conclusion: 2020, 2024, 2028 and 2032 are leap years.
