?
Solved

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

Posted on 2014-10-10
13
Medium Priority
?
125 Views
Last Modified: 2014-10-10
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.
0
Comment
Question by:ProfessorJimJam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40373743
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 40373749
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
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373762
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40373769
So, is your fiscal year starting in October?
Oct - Dec ==== Q1
Jan - Mar ==== Q2
Apr - Jun ==== Q3
Jul - Sep ==== Q4
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373776
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40373784
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
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373827
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40373848
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
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373856
yes yes.   second month listed to be returned by formula

sorry for causing confusion Glenn
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1200 total points
ID: 40373879
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
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 800 total points
ID: 40373962
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
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40374016
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
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40374020
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question