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
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
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
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")
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
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.
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Instead of concatenating "/1", couldn't you concatenate "/13" so that it could never be mistaken for a month number no matter the locale?Originally Posted by Excel Fox
Good point, Rick.
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
How about +2 with MONTH,
=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})*29,"mmmm")
Nice one Haseeb. Yes that should work too.
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Or, combining your approach with Admin's idea, even this...
=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?)...
=1*(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13")
and format the cell.
Last edited by Rick Rothstein; 04-16-2012 at 01:30 AM.
Posting Permissions
Reply With Quote
Bookmarks