Excel: How to Calculate Number of Quarters Between Two Dates

You can use the following formula to calculate the number of quarters between two dates in Excel:

```=FLOOR(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))/3,1)
```

This particular formula calculates the number of quarters between the starting date in cell A2 and the ending date in cell B2.

The following example shows how to use this formula in practice.

Example: How to Calculate Number of Quarters Between Two Dates in Excel

Suppose we have the following columns of start and end dates in Excel:

Suppose we would like to calculate the number of quarters between each start and end date.

We can type the following formula into cell C2 to do so:

`=FLOOR(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))/3,1)`

We can then click and drag this formula down to each remaining cell in column C:

Column C now displays the number of quarters between each start and end date.

For example:

• There are 8 quarters between 1/1/2018 and 2/15/2020.
• There are 3 quarters between 3/12/2019 and 2/19/2020.
• There are 0 quarters between 3/15/2019 and 4/16/2020.

And so on.

How This Formula Works

Recall the formula that we used to calculate the number of quarters between the start date in cell A2 and the end date in cell B2:

`=FLOOR(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))/3,1)`

Here is how this formula works:

First, we use YEAR(B2)*12+MONTH(B2) to calculate the total number of months between 1/1/1900 and the date in cell B2. This returns 24242.

Then, we use YEAR(A2)*12+MONTH(A2) to calculate the total number of months between 1/1/1900 and the date in cell A2. This returns 24217.

Then we subtract these two values to get 24,242 – 24,217 = 25.

This tells us there are 25 months between the two dates. Then we divide by 3 to get the number of quarters, which is 25 / 3 = 8.333.

Then we use the FLOOR function to round this number down to one significant digit, which returns 8.

This represents the number of full quarters between the start date in cell A2 and the end date in cell B2.

The formula repeats this process for each row.