Need expert helpâ€”fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

i have came up with this long formula ="Q"&INT((MONTH(MID(A$1,FI

i was wondering if there is any shortcut to achieve the same result without having this long formula.

-Glenn

MID(A$1,FIND("-",A$1)+1,3)

could probably be replaced with

MID(A$1,FIND("-",A$1)+1,99

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.

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?

Oct - Dec ==== Q1

Jan - Mar ==== Q2

Apr - Jun ==== Q3

Jul - Sep ==== Q4

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

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.

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

So, to be clear, you want the quarter for the

-Glenn

MID(A$1,FIND("-",A$1)+1,99

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

="Q"&INT((MONTH(MID(A$1,FI

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,FI

LEN 70 ="Q"&INT((MONTH(MID(A$1,FI

LEN 64 ="Q"&INT((MONTH(MID(A1,5,3

thank you veyr much.

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.

All Courses

From novice to tech pro — start learning today.

="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