1
I'm trying to input financial statements into excel and want to do it quarterly. What would be the best formula that I can continue to drag right that would give me something like:
Q1'18 | Q2'18 | Q3'18 | Q4'18 | Q1'19
Ideally the code can be used to start on any quarter like Q1'10 for example
user3628240
Posted 2019-06-06T00:36:29.850
Reputation: 113
1
in A1 put 1/1/2019. In B1 :
=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
and drag right-wards. in A2 :
="Q"&CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)&"'"&RIGHT(YEAR(A1),2)
and drag right-wards.
Hope you get what you need.. ( :
p._phidot_
Posted 2019-06-06T00:36:29.850
Reputation: 948
0
You can use below formula to increment quarter in each cell, just fill initial value in first cell then insert formula for all cells in the right.
="Q"&(MOD(MID(A1,2,1),4)+1)&"'"&RIGHT(A1,2)+IF(MID(A1,2,1)="4",1,0)
Máté Juhász
Posted 2019-06-06T00:36:29.850
Reputation: 16 807