To find the quarter months March, June, September, December from a given date, use
=TEXT(SMALL(({3,6,9,12})*29,CEILING(MONTH(A1)/3,1)),"mmmm")
Where A1 contains your date
Printable View
To find the quarter months March, June, September, December from a given date, use
=TEXT(SMALL(({3,6,9,12})*29,CEILING(MONTH(A1)/3,1)),"mmmm")
Where A1 contains your date
another one ..
=DATEVALUE(LOOKUP(MONTH(A1),{1,4,7,10},{3,6,9,12}) &"/1")
and format the cell
or
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10},{3,6,9,12})&"/1","mmmm")
Nice one with the "/1". An information for posterity: For some non-English languages, you'll always get a January as result. So with a slight modification, the safest bet would be to use a multiplier 29.
Instead of concatenating "/1", couldn't you concatenate "/13" so that it could never be mistaken for a month number no matter the locale?Quote:
Originally Posted by Excel Fox
Good point, Rick.Quote:
Originally Posted by Rick Rothstein
How about +2 with MONTH,
=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})*29,"mmmm")
Nice one Haseeb. Yes that should work too.
Or, combining your approach with Admin's idea, even this...Quote:
Originally Posted by Haseeb Avarakkan
=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13","mmmm")
Yes Rick, possible.
Another one with COUPNCD
=COUPNCD(A1,DATE(YEAR(A1),12,31),4,1)
format the cell.
Okay, if we are willing to let the Cell Format provide the month name (for display purposes only, I guess), then we can reduce the function down to only two function calls (the less function calls, the more efficient, right?)...Quote:
Originally Posted by Haseeb Avarakkan
=1*(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13")
and format the cell.