Calculate the Day of the Year in Excel

Working with dates is an important skill for anyone working with Excel. In this tutorial, you’ll learn how to use Excel to calculate the day of the year in Excel for any given date. This can be very helpful when you need to understand how far into the year a given date is.

By the end of this tutorial, you’ll have learned:

• How to calculate the day of the year in Excel
• How to find the nth day of a year in Excel

How Excel Stores Dates

Excel has a clever way of storing date and time values. The date portion is stored as an integer value, with January 1, 1900, as the first date. Any date beyond that is incremented by 1. For example:

• January 3, 1900, would be equal to 3
• November 23, 2023, would be 45253

This is important because it allows you to understand how we can calculate how many days into a year a given date is. Since dates are stored as numbers, we can simply subtract dates to determine how many days are between them.

Calculate the Day of the Year in Excel

Let’s break down how this works by taking a look at breaking down the function. Imagine that we want to calculate what day of the year January 9, 2023 is. We’re using a date that falls early in the year to better explain how this works.

= A2 - DATE(YEAR(A2), 1, 0)
= Jan 9, 2023 - DATE(YEAR(Jan 9, 2023), 1, 0)
= 44935 - DATE(2023, 1, 0)
= 44935 - Dec 31, 2022
= 44935 - 44926
= 9

We can see, by following the code above, how this function works. It calculates the date for December 31 of the previous year and subtracts that value from the date you’re using. We use December 31 of the previous year since the subtraction is inclusive.

It may not seem intuitive to pass in a day of 0 into the YEAR() function. Normally, you may see others passing in 1 in order to get the first day of the year. Really what this does is just return the last day of the month preceding that month. Remember, the YEAR() function provides one of many ways to get the year from a date in Excel.

Let’s take a look at how we can use Excel to calculate the day of the year of a given date:

We can see how easy Excel makes it to calculate the day of the year using simple subtraction. This is a simple, intuitive way to get the difference between two dates.

How to Get the nth Day of the Year in Excel

Let’s now take a look at how we can calculate the nth day of a year. This can be helpful when you want to know what, say, the fortieth day of the year is. What’s great about this is that it takes into account leap years, giving you the right result regardless of the year.

In the example below, we want to calculate what date the 9th day of the year 2023 is:

Let’s take a look at how the function works, step-by-step:

1. We calculate the value for December 31 of the previous year, meaning December 31, 2022
2. We then add 9 to this value to find the 9th day of the year
3. By hitting the Enter key, we find the 9th day of the year

Conclusion

In this tutorial, you learned how to calculate the day of the year in Excel. Knowing how to do this is an important skill to be able to, say, track progress against a project or a goal. You learned how to use date subtraction coupled with the YEAR() function to get the day of the year. From there, you learned the reverse of this process. By this, I mean, how to calculate what date the n^th day of the year is.

Additional Resources

To learn more about related topics, check out the tutorials below:

• How to Get the Day of the Week From a Date in Excel
• How to Get the First Day of the Month in Excel
• Excel YEAR() Function: Official Documentation