How to calculate numbers of quarters between two dates in google sheets?-CodePudding

I am trying to determine the number of quarters between two dates in google sheets with Q1 being Jan 1 2022 to March 31 2022 & so on for Q2, Q3, Q4.

For example: Date1: 06/14/2022 & Date2: 10/12/2022, the result should be 3 i.e., Q2, Q3, Q4.

Any advise on how this can be achieved.

I tried something like below but doesnt work

=CEILING(((YEAR(V3)*12 MONTH(V3))-(YEAR(U3)*12 MONTH(U3)))/3)

CodePudding user response：

For a simple one in the same year, try

=int((month(V3)-1)/3)-int((month(U3)-1)/3) 1

=QUOTIENT(month(V3)-1,3)-quotient(month(U3)-1,3) 1

CodePudding user response：

try:

=ARRAYFORMULA(TRANSPOSE(QUERY(QUERY(SPLIT(FLATTEN(ROW(A1:A10)&"×"&IFNA(VLOOKUP(
 IF(SEQUENCE(1, 1000, 0)<=DAYS(B1:B10, A1:A10), A1:A10 SEQUENCE(1, 1000, 0), ), 
 SPLIT(FLATTEN({"1/1/", "1/4/", "1/7/", "1/10/"}&
 SEQUENCE(4, 1, 2020)&"×"&{"Q1","Q2","Q3","Q4"}), "×"), 2, 1))), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"), "offset 1", 0)))