How do I calculate the number of quarters between two dates in Excel?

By /

Calculating the number of quarters between two dates in Excel is a simple process that can be done using the DATEDIF function. This function calculates the difference between two dates in terms of a specific time unit, in this case, quarters. By inputting the starting and ending dates, along with the “Q” parameter to indicate quarters, the DATEDIF function will return the number of quarters between the two dates. This is a convenient tool for financial analysis or budget planning, as it allows for quick and accurate calculations of the time period in terms of quarters.

Excel: 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:

Excel number of quarters between two dates

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Calculate Number of Quarters Between Two Dates
  2. How can I count the number of days between two dates, including the start and end dates, using Excel?
  3. Can dates be filtered between two specific dates in Power BI?
  4. How can I calculate the number of years and months between two dates using Excel?
  5. Excel: Calculate Years & Months Between Two Dates
  6. How do I calculate the number of weeks between two dates in Excel?
  7. How to calculate the difference between two dates in Excel?
  8. How can I calculate the number of days between two dates in Power BI?
  9. How can I calculate the number of working days between two dates in Power BI?
  10. How can I use Google Sheets to calculate the number of weeks between two given dates?