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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hnasrConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
GrahamMandenoConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.