Convert Quarter into Date

Hi Experts,

How do I convert a text data (ex. 10Q1, 10Q2, 10Q3) into a Date (ex. Jan 2010)?  

I am facing the challenge of converting a quarterly sales forecast into a monthly forecast.  
The report has a project starting date (in 10Q1, 10Q2, 10Q3..) and an estimated annual usage.  By dividing the annual usage by 12, I get the monthly usage.  
But how do I convert "10Q1" into "Jan 2010"?  "10Q2" into "April 2010"?  and "10Q3" into "July 2010"?  

Is there a way I can have them automatically populate the attached format?  

   
example.xlsx
JuJuChiaAsked:
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.

Richard DanekeTrainerCommented:
There are several approaches that can be offered.  However, Ithe following formula will work;
=IF(RIGHT(G2,1)="1","Jan ",IF(RIGHT(G2,1)="2","Apr ",IF(RIGHT(G2,1)="3","Jul ","Oct "))) & "20" & LEFT(G2,2)
as displayed in the attached workbook
ExampleDate.xlsx
0
Richard DanekeTrainerCommented:
If you are doing this in Access, use a query and use IIF (two i's and f) in place of the IF in the formula.
0
hnasrCommented:
This is done using a lookup table:
K                   L
Q1      Jan
Q2      April
Q3      July
Q4      Oct

Using a new column
=LOOKUP(RIGHT(G2;2);$K$1:$K$4;$L$1:$L$4) & " " &LEFT(G2;2)+2000

example-2.xlsx
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.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GrahamMandenoCommented:
I suggest you use a VBA function such as this:
Public Function QuarterToDate(qtr As Variant) As Variant
Dim pQ As Integer
Dim s1 As String, s2 As String
Dim iYear As Integer, iQtr As Integer
pQ = InStr(qtr & "", "Q")
If pQ <> 0 Then
  s1 = Left(qtr, pQ - 1)
  s2 = Mid(qtr, pQ + 1)
  If IsNumeric(s1) And IsNumeric(s2) Then
    iYear = CInt(s1)
    iQtr = CInt(s2)
    If iYear < 100 Then iYear = iYear + 2000
    QuarterToDate = DateSerial(iYear, (iQtr - 1) * 3 + 1, 1)
  End If
End If
End Function
This will return a date (the first day of the quarter) if the string passed is valid:
QuarterToDate( "10Q1" ) gives 01-Jan-2010
QuarterToDate( "8Q2" )   gives 01-Apr-2008
etc...
Having a date is more useful than a string (e.g. "Jan 2010") because you can sort them and do date arithmetic with them.
If you want to convert the date to a string, use the Format function:
Format( QuarterToDate( "10Q1" ), "mmm yyyy" ) gives "Jan 2010"
--
Graham
0
JuJuChiaAuthor Commented:
Hi DoDahD and hnasr,
Thank you for both of your inputs.  Both of your functions will work, but I should have mentioned that I am trying to do this conversion in Access, and hoping to utilize the Date formulas in Access.  

Hi Graham,
Thank you for your formula.  Can you tell me if I cut-and-paste the formula into the SQL View window of a new Access query?  Or in the Criteria section of in the Design View?  

Thanks,
JujuChia
0
GRayLCommented:
This works for me:

dateserial(left(yyqq,2),right(yyqq,1)*3-2,1)
0
GRayLCommented:
yyqq of course being '10Q3' or what ever.
0
JuJuChiaAuthor Commented:
Hi All,

On a second thought, maybe I can do the conversion in Excel first, then import the spreadsheet into Access with the Date in "Jan 2010", "April 2010", and "Oct 2010" format.  
I know that Access will automatically convert them into "1/1/2010", "4/1/2010", and "10/1/2101" if I change the Data Type from Text to Date/Time.  

0
GRayLCommented:
Did you look at my solution?
0
GrahamMandenoCommented:
Create a new module (or open an existing one) and paste the function into it.  Then save the module (do NOT give it the same name as the function!)
Then, in your query, include a calculated field like this:
QtrDate: QuarterToDate( [QtrField] )
Where [QtrField] is the field in your table that contains the "yyQn" string.
You can do the calculation "in-line" in your query, but calling a function gives you more flexibility with handling different formats and possibly invalid data.  For example, Ray's expression will handle "08Q3", but not "8Q3" or "1999Q3".
--
Graham
0
JuJuChiaAuthor Commented:
Hi GRayL,

I am afraid that I don't understand your reply.  Do I apply your formula into my Excel spreadsheet, or Access query?  You may have to provide more directions as I am a beginner.  Thanks.  
0
GRayLCommented:
Why are you guys always so fast on the trigger?  Remember, elegance come by one slowly.  See you in the next thread.
0
Richard DanekeTrainerCommented:
On to the next thread!  If this is a one time conversion, a quick simple fix may be preferred to elegance!
BTW
NewDate : IIF(RIGHT(G2,1)="1","Jan ",IIF(RIGHT(G2,1)="2","Apr ",IIF(RIGHT(G2,1)="3","Jul ","Oct "))) & "20" & LEFT(G2,2)
will work in an Access query and if the query were an append or maketable query, will add the records to data table.
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 Access

From novice to tech pro — start learning today.