Mod or similar formula to convert fiscal year month to calendar year month
I need help figuring out how to convert my companies fiscal year month (and quarter) to calendar year month (and quarter) using mod or similar function. I already know how to do this using the if function.
I figured out how to convert CY to FY using mod but for the life of me cannot figure out FY to CY. I tried variations of the CY to FY solutions I have but cannot get to the solution.
CYQ to FYQ =1+MOD(C2,4)
CYMON to FYMON =MOD((C8)+2,12)+1
I need to figure out the reverse of what in this image and come up with something similar to my formulas, if possible. I don't want a lengthy formula or to use if.
Solution 1:
Modular arithmetic works on numbers starting with 0. For example, integers to mod 4 are 0, 1, 2 or 3. So numbering the calendar year quarters as 0 for January-March, 1 for April-June, etc and similarly numbering the financial year quarters as 0 for October-December, 1 for January to March, we get
FYQ0 = MOD(1+CYQ0,4) and CYQ0 = MOD(3+FYQ0, 4)
where, FYQ0 and CYQ0 are, respectively, the financial year and calendar year quarter numbers when numbered from zero. (Hint: to determine the constant amount to add inside the MOD function, look at the required result when the value to be converted is zero).
Conventionally, quarters are numbered from 1 to 4, not 0 to 3, so FYQ = 1+FYQ0 and CYQ = 1+CYQ0 where FYQ and CYQ are, respectively, the financial year and calendar year quarter numbers when conventionally numbered.
Substituting for CYQ0 in the equation for FYQ0 yields
FYQ0 = MOD(1+(CYQ-1),4) = MOD(CYQ, 4)
So that
FYQ = 1+FYQ0 = 1+MOD(CYQ,4) [Equation 1]
The same process for CYQ yields
CYQ0 = MOD(3+(FYQ-1), 4) = MOD(2+FYQ, 4)
and
CYQ=1+MOD(2+FYQ,4) [Equation 2]
For month numbers, a similar process leads to
FYMON0 = MOD(3+CYMON0,12)
and
CYMON0 = MOD(9+FYMON0, 12)
so that
FYMON = 1 + MOD(2+CYMON, 12) [Equation 3]
and
CYMON = 1 + MOD(8+FYMON, 12) [Equation 4]
The four equations provided the basis of formulas for converting quarter/month numbers between financial years and calendar years when conventional numbering of 1-4 is used for quarters and 1-12 for months.