Table of Contents
To get the first day of the quarter in Excel, use the EDATE function to add or subtract a certain number of months from the starting date. For example, to get the first day of the first quarter, use the EDATE function with 0 months added to the starting date. To get the first day of the second quarter, use the EDATE function with 3 months added to the starting date. This method can be used for any quarter by adjusting the number of months added or subtracted accordingly. This function is useful for organizing data or creating reports based on quarterly time frames in Excel.
Get First Day of Quarter in Excel (With Example)
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
Additional Resources
The following tutorials explain how to perform other common operations in Excel: