Subtracting Fiscal Quarters from eachotehr

[Alex8112]

I am looking to just calculate the column in red below (Column E) in a basic excel spreadsheet.

So I have the information in column A which is converted from date to fiscal quarter for column B. The same applies for column C which is converted into fiscal quarter (Column D). The end result is the difference between column B and column D to get the number of quarters. Do you think this is possible please?

I am currently manually entering the red E column as I get hung up when the quarters cross years.

Any help would be GREATLY appreciated!

First Reported Date (A)​	First Reported Qtr (B)​	Target Completion Date (C)​	Target Completion Qtr (D)​	Duration to Remediate (Quarters) (E)​
30/03/2016​	Q2 F16​	19/05/2016​	Q3 F16​	1​
30/03/2016​	Q2 F16​	20/06/2016​	Q3 F16​	1​
18/08/2014​	Q4 F14​	31/12/2015​	Q1 F16​	5​
28/03/2016​	Q2 F16​	31/12/2016​	Q1 F17​	3​

<tbody> </tbody>

 

[MarcelBeug]

Based on B1 and D1, enter in E1 and copy down::

=(RIGHT(D1,2)-RIGHT(B1,2))*4+MID(D1,2,1)-MID(B1,2,1)

OR:

Based on A1 and C1 (assuming fiscal quarters start on 1/10 1/1 1/4 and 1/7), enter in E1 and copy down:

=(YEAR(C1)-YEAR(A1))*4+INT((2+MONTH(C1))/3)-INT((2+MONTH(A1))/3)

 

[Alex8112]

Based on B1 and D1, enter in E1 and copy down::

=(RIGHT(D1,2)-RIGHT(B1,2))*4+MID(D1,2,1)-MID(B1,2,1)

OR:

Based on A1 and C1 (assuming fiscal quarters start on 1/10 1/1 1/4 and 1/7), enter in E1 and copy down:

=(YEAR(C1)-YEAR(A1))*4+INT((2+MONTH(C1))/3)-INT((2+MONTH(A1))/3)

IT WORKS!
Thank You Marcel!! Good karma to you!!

And I can't believe how fast that response was!
You're a gentleman and a scholar!

~Alex

 

[MarcelBeug]

You're welcome.