CODEWITHSUNDEEP
exceldategoogle-sheetsformula
Brandon C
Brandon C

Reputation: 1

Returning Previous Quarter and Year Google Sheets

I am having trouble writing a formula in Google sheets that will return the previous quarter based on today's date.

For example, if I have cell A1 as =today() which is 1/26/22, I would like cell B1 to return the previous quarter "Q4 2021".

Thanks in advance!

Upvotes: 0

Views: 869

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

I didn't hear back from you on my comment to your original post. But assuming that you are, in fact, using Google Sheets and that you are actually only needing the return for one cell (A1), this should work in B1:

=VLOOKUP(MONTH(A1),{1,"Q4";4,"Q1";7,"Q2";10,"Q3"},2,TRUE)&" "&YEAR(A1)-IF(MONTH(A1)<4,1,0)

If you were trying to get such a return for an entire columnar range (say, A2:A with a heading in A1), you could use this version in B1:

=ArrayFormula({"Prev. Quarter"; IF(A2:A="",,VLOOKUP(MONTH(A2:A),{1,"Q4";4,"Q1";7,"Q2";10,"Q3"},2,TRUE)&" "&YEAR(A2:A)-IF(MONTH(A2:A)<4,1,0))})

Upvotes: 1

user16938924
user16938924

Reputation:

Here you go... just copy and paste this formula in B1:

=IFS(
    A1="","",
    AND(MONTH(A1)>=1,MONTH(A1)<=3),"Q4 "&YEAR(A1)-1,
    AND(MONTH(A1)>=4,MONTH(A1)<=6),"Q1 "&YEAR(A1),
    AND(MONTH(A1)>=7,MONTH(A1)<=9),"Q2 "&YEAR(A1),
    AND(MONTH(A1)>=10,MONTH(A1)<=12),"Q3 "&YEAR(A1)
)

Upvotes: 0

Related Questions