0

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.

Jessie
  • 1,463
David.L
  • 11

1 Answers1

2

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.

Evargalo
  • 2,593
  • Hi Evargalo,

    Thanks for reply. Is there a way to take 'Dec', 'Apr' and 'Aug' as the numeric month itself, i.e 12, 4 and 8 respectively instead of 0, 1 and 2?

    Reason being, I need to incorporate this formula in Excel to get the quarters.

     – David.L Jul 21 '17 at 14:19
  • Yes sure. I'll edit. – Evargalo Jul 21 '17 at 14:21
  • Thanks Evargalo!

    Also, my apologies for being stupid here, but am I correct to assume that the value of the floor is then divided by 4, and the remainder is added to 1?

    If it's the case, for some reason, I have for n = 12 (Dec) for Dec year end, I have 1 as the quarter.

    f(0,12) = 1 +[floor((12-4*0)/3)]mod4 = 1 + [floor(12/3)mod4 = 1 + 0 = 1

     – David.L Jul 21 '17 at 14:26
  • 'mod 4' means that you divide by 4 but you consider the rest, not the quotient. For instance $17 mod 4=1$ because $17=4*4+1$. – Evargalo Jul 21 '17 at 14:27
  • Ok...so my understanding of mod is correct then, that is to take the remainder.

    That being said, for Dec year end, and for Dec month, I do get 1 as the value instead of 4.

    Is my understanding of the formula wrong then?

    Isn't 1 + floor(12/3 = 4)mod4 results in 1+0=1?

     – David.L Jul 21 '17 at 14:42
  • Tried it many times, and it's giving me Dec as Quarter 1 for Dec Year End.

    The same with April Year End, for the month of Apr, it's giving me Quarter 1 instead of Quarter 4.

    In summary, it's off by 1.

     – David.L Jul 21 '17 at 16:09
  • oh, sorry, I'm sure you have added the '+1' by yourself, but I'll edit and correct my answer. – Evargalo Jul 24 '17 at 07:15
  • Can you check the formula I've just edited and confirm whether the result is appropriate ? – Evargalo Jul 24 '17 at 08:15