Calculate the date of the current quarter in Excel

To return on the first day of the current quarter, we must build a custom date with the MOD function and the Date function. According to the date of today, the 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 done to return the first Monday of the current quarter. To do this, 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

• Next Monday
• Monday in 2 weeks
• Previous Monday
• Monday 2 weeks ago
• First Monday of the month