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!

[TABLE="width: 672"]
<tbody>[TR]
[TD="width: 183"]

First Reported Date (A)​

[/TD]
[TD="width: 138"]

First Reported Qtr (B)​

[/TD]
[TD="width: 189"]

Target Completion Date (C)​

[/TD]
[TD="width: 161"]

Target Completion Qtr (D)​

[/TD]
[TD="width: 225"]

Duration to Remediate (Quarters) (E)​

[/TD]
[/TR]
[TR]
[TD="width: 183"]

30/03/2016​

[/TD]
[TD="width: 138"]

Q2 F16​

[/TD]
[TD="width: 189"]

19/05/2016​

[/TD]
[TD="width: 161"]

Q3 F16​

[/TD]
[TD="width: 225"]

1​

[/TD]
[/TR]
[TR]
[TD="width: 183"]

30/03/2016​

[/TD]
[TD="width: 138"]

Q2 F16​

[/TD]
[TD="width: 189"]

20/06/2016​

[/TD]
[TD="width: 161"]

Q3 F16​

[/TD]
[TD="width: 225"]

1​

[/TD]
[/TR]
[TR]
[TD="width: 183"]

18/08/2014​

[/TD]
[TD="width: 138"]

Q4 F14​

[/TD]
[TD="width: 189"]

31/12/2015​

[/TD]
[TD="width: 161"]

Q1 F16​

[/TD]
[TD="width: 225"]

5​

[/TD]
[/TR]
[TR]
[TD="width: 183"]

28/03/2016​

[/TD]
[TD="width: 138"]

Q2 F16​

[/TD]
[TD="width: 189"]

31/12/2016​

[/TD]
[TD="width: 161"]

Q1 F17​

[/TD]
[TD="width: 225"]

3​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.