Subtracting Fiscal Quarters from eachotehr

Alex8112

New Member
Joined
Jun 2, 2016
Messages
3
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>
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Based on B1 and D1, enter in E1 and copy down::
Code:
=(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:
Code:
=(YEAR(C1)-YEAR(A1))*4+INT((2+MONTH(C1))/3)-INT((2+MONTH(A1))/3)
 

Alex8112

New Member
Joined
Jun 2, 2016
Messages
3
Based on B1 and D1, enter in E1 and copy down::
Code:
=(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:
Code:
=(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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,889
Messages
5,598,672
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top