Given the calendar year end, which is Dec, we know that Quarter 1 is Jan – Mar, Quarter 2 is Apr – Jun, Quarter 3 is Jul – Sep and Quarter 4 is Oct – Dec.
My question is, is there a formula to get the correct quarter given the Year End (i.e. Apr Year End, Aug Year End, Dec Year End etc) and given the current month, it knows which quarter it'll be.
Below is the table of the quarters based on each month given it's year end.
\begin{array}{|c|c|c|c|}
\hline
& Dec\ (Year\ End) & Apr\ (Year\ End) & Aug\ (Year\ End) \\ \hline
Jan & Q1 & Q3 &Q2\\ \hline
Feb & Q1 & Q4 &Q2\\ \hline
Mar & Q1 & Q4 &Q3\\ \hline
Apr & Q2 & Q4 &Q3\\ \hline
May & Q2 & Q1 &Q3\\ \hline
Jun & Q2 & Q1 &Q4\\ \hline
Jul & Q3 & Q1 &Q4\\ \hline
Aug & Q3 & Q2 &Q4\\ \hline
Sep & Q3 & Q2 &Q1\\ \hline
Oct & Q4 & Q2 &Q1\\ \hline
Nov & Q4 & Q3 &Q1\\ \hline
Dec & Q4 & Q3 &Q2\\ \hline
\end{array}
Please note that for Dec Year End, it means that Jan 1st is the start and Dec 31 is the end date.
Apr Year End, means that May 1 is the start and Apr 30th is the Year End.
Best Answer
Let $x$ take the values $12,4,8$ when the years ends in Dec, Apr and Aug respectively.
Let $n=1,...,12$ index the months Jan,...,Dec.
Then the function $f(x,n)=1+floor[((n-1-x)mod12)/3]$ gives you the number of the quarter the month $n$ is in.