You can use the following formula to get the first day of the quarter for a specific date in Excel:
=DATE(YEAR(A2),FLOOR.MATH(MONTH(A2)-1,3)+1,1)
This particular formula will return the first day of the quarter for the date in cell A2.
For example, if cell A2 contained the date 1/14/2023 then this formula would return 1/1/2023 since this represents the first day of the quarter that 1/14/2023 belongs in.
The following example shows how to use this formula in practice.
Example: How to Get First Day of Quarter in Excel
Suppose we have the following column of dates in Excel:
We can type the following formula into cell B2 to get the first day of the quarter for the date in cell A2:
=DATE(YEAR(A2),FLOOR.MATH(MONTH(A2)-1,3)+1,1)
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays the first day of the quarter for each corresponding date in column A.
Recall that there are four quarters in a year:
- Q1: January – March
- Q2: April – June
- Q3: July – September
- Q4: October – December
Thus, the first day of the quarter for each date will fall on one of the following dates:
- January 1st
- April 1st
- July 1st
- October 1st
Notice that each of the cells in column B contains one of these dates, since these are the first days of each quarter.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Get First Day of Week in Excel
How to Get First Day of Month in Excel
How to Get First Business Day of Month in Excel
