Search code examples
dategoogle-sheetsgoogle-sheets-formulags-vlookup

How can you define custom Quarters in formula without helper columns?

I'm trying to calculate how much is left to close in terms of business in this current quarter.

To do this, I need to identify where we are in the quarter, how much has been closed and how much is left until the end of the quarter.

The problem is our Financial Year starts on Feb 1 and ends Jan 31st -

I tried this formula but won't work in my case. 

=ROUNDUP(MONTH(*reference or date*)/3;0)

Any thoughts on how I can go around this? Also, how can I identify where in the quarter we are to calculate Quota for Q minus QTD?

Solution

  • The standard formula is 

    =int((month(A2)+2)/3)

    So the adjusted formula (in B2) would be obtained by going back one month:

    =int((month(eomonth(A2,-1))+2)/3)

    The quarter end is given by multiplying the quarter by 3 to get the month and going forward one month:

    =eomonth(date(year(A2),B2*3,1),1)

    enter image description here

    EDIT

    The year for the end quarter of January in C2 is clearly incorrect - you could fix it by putting

    =eomonth(date(year(A2)-(month(A2)<2),B2*3,1),1)

    enter image description here