Calculate number of quarters between two dates that cross over years

[vmoore845]

I have used the following formula:

=Floor(((Year(End date)*12+Month(End Date))-(Year(Beginning Date)*12+Month(Beginning date)))/3,1)

Example: Begin Date: 7-1-20. End Date: 6-30-21. The formula is calculating 3 Quarters, it should be 4 quarters, what did I do wrong? Thank you!!!

 

[earthworm]

Try this

Book1
	A	B	C
1	01-07-2020	30-06-2021	4
Sheet1

Cell Formulas
Range	Formula
C1	C1	=ROUND(DATEDIF(A1,B1,"M")/3,0)

 

[Dave Patton]

consider the following

T202109a.xlsm
	A	B	C
1	1-Jul-20	30-Jun-21	4
2
2d

Cell Formulas
Range	Formula
C1	C1	=DATEDIF(A1,B1+1,"M")/3

 

[vmoore845]

Using this formula is not allowing me to pickup a partial quarter - begin date 5/17/21 - shows 0 quarters and I need it to count as 1 quarter; in other words if there is one day in a previous or subsequent quarter I need that to count as a quarter. Thank you

 

[Alex Blakenburg]

Using this formula is not allowing me to pickup a partial quarter - begin date 5/17/21 - shows 0 quarters and I need it to count as 1 quarter; in other words if there is one day in a previous or subsequent quarter I need that to count as a quarter. Thank you

Can you please clarify, 5/17/21 is not in a previous quarter to 6/30/21.
Do you still want that to count as 1 ?

 

[Phuoc]

From 30/6/2021 to 1/7/2021 => Do you expect the result to be 1 or 2?

 

[vmoore845]

Can you please clarify, 5/17/21 is not in a previous quarter to 6/30/21.
Do you still want that to count as 1 ?

We are giving bonuses to employees that worked from 7/1/2020 thru 6/30/2021; if they worked one day in any quarter, they get credit for that quarter. The bonuses will be paid based upon the number of quarters worked and FTE count. If an employee was hired and or/worked 3/29/21, then they would get credit for working the entire 1st quarter and therefore would get a total of 2 quarters credit towards their bonus. I hope that makes sense. Thank you

 

[vmoore845]

Can you please clarify, 5/17/21 is not in a previous quarter to 6/30/21.
Do you still want that to count as 1 ?

yes, sorry, even though it is not a previous quarter it is at least one day in a quarter within the covered period 7/1/2020 thru 6/31/21. Thank you

 

[Alex Blakenburg]

I think @Phuoc is ahead of the rest of us (or at least me), based on the question Phuoc asked.

Try this.

20210908 Inconsistent Qtr difference calculation.xlsx
	A	B	C
1	start	end	Eligible Quarters
2	1/07/2020	30/06/2021	4
3	1/07/2020	1/07/2021	5
4	1/07/2020	1/07/2021	5
5	17/05/2021	30/06/2021	1
6	10/06/2021	30/06/2021	1
Final

Cell Formulas
Range	Formula
C2:C6	C2	=(YEAR(B2)*4+CEILING(MONTH(B2)/3,1))-(YEAR(A2)*4+CEILING(MONTH(A2)/3,1))+1