How do you calculate the current quarter, the next quarter, or the previous quarter in Excel?
In this article, you’ll learn formulas to determine which quarter a date belongs to. We’ll show you how to use Excel functions like DATE and MOD to do this quickly and easily.
Calculate the date of the current quarter in Excel
- To determine the first day of the current quarter in Excel, use a custom date formula that combines the MOD and DATE functions 😉
- Based on today’s date, the Excel formula is:
=DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3),1)
The key point of the formula is the calculation of the month. To the current month, you subtract 0, 1, or 2 months according to the result of the MOD function.
Now, if you want to return the quarter based on the value of an Excel cell (for instance, B2), the formula is
Calculate the first day of the next quarter
Deducted from the previous formula, the formula to calculate the first day of the next quarter is
=DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3)+3,1)
Calculate the first day of the next quarter
The formula for the first day of the previous quarter is
=DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3)-3,1)
First Monday of the current quarter
A more complex formula can be used to return on the first Monday of the current quarter. We will use the formula to return the next Monday.
=DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3),1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3),1)-2)+7
Formula to calculate the Quarter Number in Excel
Now, if you want to calculate the quarter number according to a specific date, you must use the ROUNDUP function.
=ROUNDUP(MONTH(A2)/3,0)
Other Articles related to the date
The following articles will give you formulae to calculate specific dates
