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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Convert Quarterly Data to Annual Data in Excel
How to Convert Date to Quarter and Year in Excel
How to Group Data by Quarter in Excel
My start date of the tenancy contract is 12/5/2024 and my end date is 11/5/2027, If I am issuing a cheque, the date is the 19th of every quarter. How many cheques I will be missing to the landlord
How can I calculate this in Excel
Hi Sunil…To calculate the number of cheques you will need to issue on the 19th of every quarter within the given tenancy period (12/5/2024 to 11/5/2027) and determine if you will miss any, follow these steps in Excel:
1. **List the dates for cheque issuance**:
– Start from 19/5/2024 and list every subsequent 19th of a quarterly period (19/5, 19/8, 19/11, 19/2).
2. **Check if these dates fall within the tenancy period**:
– Ensure the dates are between 12/5/2024 and 11/5/2027.
3. **Calculate the total number of cheques**:
– Count the valid dates within the range.
### Step-by-Step in Excel
1. **Create a list of quarterly cheque dates**:
– In Excel, enter the first cheque date in cell `A1` (e.g., 19/5/2024).
2. **Use a formula to generate the next cheque dates**:
– In cell `A2`, enter the formula `=EDATE(A1, 3)`. This adds three months to the previous date.
– Drag this formula down to generate subsequent dates.
3. **Filter the dates within the tenancy period**:
– In another column, use a formula to check if the date is within the tenancy period.
– For example, in cell `B1`, enter:
“`excel
=IF(AND(A1>=DATE(2024,5,12), A1<=DATE(2027,5,11)), "Valid", "Invalid") ``` - Drag this formula down alongside the generated dates. 4. **Count the valid dates**: - Use the `COUNTIF` function to count the "Valid" entries. ### Example in Excel 1. **Column A**: List of cheque dates. - A1: 19/5/2024 - A2: `=EDATE(A1, 3)` (drag this down to fill subsequent cells). 2. **Column B**: Validity check. - B1: `=IF(AND(A1>=DATE(2024,5,12), A1<=DATE(2027,5,11)), "Valid", "Invalid")` (drag this down to fill subsequent cells). 3. **Counting valid cheques**: - Use `=COUNTIF(B:B, "Valid")` to count how many "Valid" dates you have. Here is a snapshot of what the Excel sheet might look like: | A (Cheque Date) | B (Validity) | |-----------------|------------------| | 19/5/2024 | Valid | | 19/8/2024 | Valid | | 19/11/2024 | Valid | | 19/2/2025 | Valid | | ... | ... | | 19/2/2027 | Valid | | 19/5/2027 | Invalid (after) | This approach will help you determine how many cheques you will be issuing and if any fall outside the tenancy period. ### VBA Alternative for Automation For more automation, you can use VBA to generate and validate the dates. Here’s a simple VBA code snippet to achieve this: 1. Press `ALT + F11` to open the VBA editor. 2. Insert a new module. 3. Paste the following code: ```vba Sub CalculateCheques() Dim startDate As Date Dim endDate As Date Dim chequeDate As Date Dim chequeCount As Integer startDate = DateSerial(2024, 5, 12) endDate = DateSerial(2027, 5, 11) chequeDate = DateSerial(2024, 5, 19) chequeCount = 0 Do While chequeDate <= endDate If chequeDate >= startDate And chequeDate <= endDate Then chequeCount = chequeCount + 1 End If chequeDate = DateAdd("m", 3, chequeDate) Loop MsgBox "Total valid cheques: " & chequeCount End Sub ``` 4. Run the `CalculateCheques` macro to get the total count of valid cheques. This will help you automate the process if you prefer using VBA over manual Excel calculations.