JuJuChia
asked on
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
This works for me:
dateserial(left(yyqq,2),ri
dateserial(left(yyqq,2),ri
yyqq of course being '10Q3' or what ever.
ASKER
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.
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.
Did you look at my solution?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Why are you guys always so fast on the trigger? Remember, elegance come by one slowly. See you in the next thread.
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
will work in an Access query and if the query were an append or maketable query, will add the records to data table.
BTW
NewDate : IIF(RIGHT(G2,1)="1","Jan ",IIF(RIGHT(G2,1)="2","Apr
will work in an Access query and if the query were an append or maketable query, will add the records to data table.
=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