How to Get the First Day of the Quarter in Excel

In many industries, such as finance or accounting, being able to work with dates is an important skill. In particular, being able to find the first day of the quarter is a common task, especially in accounting. This is true, in particular, when wanting to label or classify values as belonging to a certain fiscal period. In this tutorial, you’ll learn how to get the first day of the quarter in Excel.

There are a number of easy ways in which you can get the first day of the quarter. By the end of this tutorial, you’ll have learned how to do the following:

• Use the DATE(), MONTH(), and MOD() functions to get the first day of the quarter
• Use the TODAY() function to get the first day of the current quarter

How to Get First Day of the Quarter in Excel Using DAY()

The formula may look a little bit more complex than you’d like, but it’s actually quite simple.

=DATE(YEAR(A2), MONTH(A2)-MOD(MONTH(A2),3),1)

Let’s break it down, step-by-step:

1. The DATE() function builds a date out of the year, month, and day parts
2. For the year, we pass in the year using the YEAR() function
3. Calculating the month requires a bit more work. We first get the current month using the MONTH() function. Then, we subtract the remainder by dividing the month by 3 using the MOD() function. For example, passing in the month of July (month 7) will have a remainder of 1.
4. We use 1 as the day part to return the first day

Now that you have a good understanding of how the function works, let’s take a look at an example of how you can get the first day of the quarter for a number of dates.

How to Get the First Day of the Current Quarter in Excel

We can use the same process you learned above to get the first day of the current quarter. Rather than tying our function to a particular cell, we can simply pass in the TODAY() function. The TODAY() function is used to return the current date.

Let’s see how we can use the DATE() function and the TODAY() function to get the first day of the current quarter.

Step 1: Enter the Function with the TODAY() Function to Get the First Day of the Quarter

Enter the following formula into any of the cells:

=DATE(YEAR(TODAY()), MONTH(TODAY())-MOD(MONTH(TODAY()),3),1)

Since the TODAY() function returns today’s date, we don’t have to reference another cell. The formula still works by finding the first month of the quarter using the MOD() function.

Step 2: Hit the Return Key to Get the First Day of Current Quarter

When you hit the return key, the function evaluates and gets the first day of the current quarter. When writing this article, it was November 4, 2022. Excel was able to find the first day of the quarter to be September 1, 2022. It used the MOD() function to figure out that it needed to subtract 2 from the month. This is because MOD(11, 3) will have a remainder of 2. Excel then subtracts 2 from 11 (the current month) to get September.

Conclusion

In this tutorial, you learned how to use Excel to get the first day of a quarter. Being able to work with dates is an essential skill for anyone working in Excel. Getting quarters is incredibly helpful when it comes to working with finance, but is also relevant for other industries.

In this tutorial, you first learned how to get the first day of a quarter for a column of dates. You did this by combining a number of different functions, including DATE(), YEAR(), MONTH(), and MOD(). While this sounds complicated, you learned how to break this down, step-by-step. We then walked through a hands-on example to get you up and running. From there, you learned how to get the first day of the current quarter. This was accomplished by combining the previously learned method with the TODAY() function.

Additional Resources

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

• How to Get the First Day of the Week in Excel
• How to Get the First Day of the Month in Excel
• How to Get the First Day of the Year in Excel
• Excel MOD() Function: Official Documentation