# How to replace Jan-Mar 2014 to Q1 - 2014 A Better way?

I needed to convert quarter dates in cell Jan-June 2014  to  Q1 - 2014

i have came up with this long formula  ="Q"&INT((MONTH(MID(A\$1,FIND("-",A\$1)+1,3)&RIGHT(A\$1,4))+2)/3)&" - "&YEAR(MID(A\$1,FIND("-",A\$1)+1,3)&RIGHT(A\$1,4))

i was wondering if there is any shortcut to achieve the same result without having this long formula.
LVL 27
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
How about this:
="Q"&INT((MONTH(LEFT(A\$1,FIND("-",A\$1)-1)&RIGHT(A\$1,4))+2)/3)&" - "&RIGHT(A\$1,4)
-Glenn
0
Commented:
Not much you can do to shorten text parsing but...

MID(A\$1,FIND("-",A\$1)+1,3)&RIGHT(A\$1,4))

could probably be replaced with

MID(A\$1,FIND("-",A\$1)+1,999)

which will give 'June 2014', which is not quite the same as what you originally had but I think it will work for you.

Also I'm not sure you need the year when working with the MONTH function so you might be able to take that out except when you concatentate it at the end.
0
Author Commented:
Yes, i made a mistake in the beginning, the result should have been from Jan-Mar 2014 to Q2 - 2014

Not Q1  rspahitz formula gives me the result,  but since i made a mistake Glenn formula is much shorter, however it gives me Q1

Glenn,

is there any way that you change your formula to result Q2?
0
Excel VBA DeveloperCommented:
So, is your fiscal year starting in October?
Oct - Dec ==== Q1
Jan - Mar ==== Q2
Apr - Jun ==== Q3
Jul - Sep ==== Q4
0
Author Commented:
not at all

the fiscal year starts in Jan

so my quarters are like this

Jan-Mar 2014  First Q    April-June Second Q , July to Sep Third Q and Oct to Dec Fourth Q
0
Excel VBA DeveloperCommented:
If you're on a calendar year, my formula produces that result.  The quarter is based on the first month shown.  So, "Jan" is the first month in Q1.  Why do you want it to display Q2?

The only thing I did to simplify your formula was change from MID to LEFT functions and eliminate the YEAR function at the end as it was not needed.
0
Author Commented:
again another mistake by me.  today we had staff bar, so dont blame me for it :)

what i meant was
Jan-Mar 2014 to Q1 - 2014
Jan-June 2014  to Q2 - 2014

your formula results Q1 - 2014   even for   Jan-June 2014
0
Excel VBA DeveloperCommented:
Well, that's because I'm looking at the first month and your original question had "Jan-Jun" returning Q1.  It wasn't until this post that you referenced another quarter - and then you didn't mention June; it still showed "Jan-Mar".  Hence, the confusion on your fiscal year and my follow-up question.

So, to be clear, you want the quarter for the second month listed to be returned by the formula, correct?

-Glenn
0
Author Commented:
yes yes.   second month listed to be returned by formula

sorry for causing confusion Glenn
0
Excel VBA DeveloperCommented:
Okay, try this:
="Q"&INT((MONTH(MID(A1,5,3)&RIGHT(A1,4))+2)/3)&" - "&RIGHT(A1,4)

Produces the quarter that the second-listed month falls in on a calendar year.

-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
I think my original post would have helped.  All you need to do is find the "-" and take everything after it:

MID(A\$1,FIND("-",A\$1)+1,999)

Take the month of that and +2 /3 like you're doing:

="Q"&INT((MONTH(MID(A\$1,FIND("-",A\$1)+1,999))+2)/3)&" - "&RIGHT(A\$1,4)
0
Author Commented:
Thank you very much both.

first one arrived raspahits
second one is also raspahits
third line is most shortest version Glenn

here are the outcome

LEN      92      ="Q"&INT((MONTH(MID(A\$1,FIND("-",A\$1)+1,999))+2)/3)&" - "&YEAR(MID(A\$1,FIND("-",A\$1)+1,999))
LEN      70      ="Q"&INT((MONTH(MID(A\$1,FIND("-",A\$1)+1,999))+2)/3)&" - "&RIGHT(A\$1,4)
LEN      64      ="Q"&INT((MONTH(MID(A1,5,3)&RIGHT(A1,4))+2)/3)&" - "&RIGHT(A1,4)

thank you veyr much.
0
Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 300 points for Glenn Ray's comment #a40373879
Assisted answer: 0 points for ProfessorJimJam's comment #a40373856
Assisted answer: 200 points for rspahitz's comment #a40373962

for the following reason:

Thank you.  i have now the shorter version.   your quick and professional responses are much appreciated.

thank you both and have a great weekend.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.