How to define 90Q1 as a date?

G

gara

I need to tell Excel that 90Q1 is a date. It refers to the first quarter of
1990. I need it in order to import the data to a pivot table.
Thanks
 
G

gara

Hi,
I am importing the data from another program and it appear as 90Q1. I want
to put the data to a pivot table.
I need it to appear as quarters because I have more data that is monthly and
I want to differenciate them.

Any suggestion?

Thanks,

Gara
 
M

Myrna Larson

90Q1 is NOT a date to Excel and there's no way to convince Excel it is other
than by changing, to, as Bernard suggests, some date within the quarter.

OTOH, if you have a header, say, "Qtr", that field can contain text entries
like 90Q1, 91Q4, etc. You can still use this field as a row or column field
and group on it, even though the entries are not dates.
 
D

Dave Peterson

If the first qtr starts with Jan, 2nd with April, 3rd with July and 4th with
Oct, you could use a formla like:

=DATE(IF(--LEFT(A1,2)<70,"20","19")&LEFT(A1,2),(RIGHT(A1,1)-1)*3+1,1)

(format it as a date)
 
D

Dave Peterson

I put this in A1:b16:
QTR something
90Q2 2
90Q3 3
90Q4 4
91Q1 5
91Q2 6
91Q3 7
91Q4 8
90Q1 9
90Q2 10
90Q3 11
90Q4 12
90Q1 13
90Q2 14
90Q3 15
90Q4 16

I created a pivottable and got this:

Sum of something
QTR Total
90Q1 22
90Q2 26
90Q3 29
90Q4 32
91Q1 5
91Q2 6
91Q3 7
91Q4 8
Grand Total 135

xl2002 wouldn't let me group on that QTR field.
 
M

Myrna Larson

If you want totals by quarter, that's what you already have. You don't need to
do any grouping.

The Pivot table has already added the values from column B for each unique
quarter, e.g. the values for 90Q1 = 9 + 13 = 22; for 90Q2, 2 + 10 + 14 = 26,
etc.

However, since this is a text field, you can impose any grouping you want (to
take a ridiculous example, group 90Q2 with 91Q4) by CTRL-clicking on each of
the quarters to be put into a single group, then go to the Group function.
 
D

Dave Peterson

I get you.

I thought that you were gonna show me a neat way of grouping those 90Q# into
just 1990.

I think I like converting to dates and using the "group by" stuff better.
 
G

gara

Hi,

At the end what I did is run the following macro:

Range("C210:C379").Select
Selection.Copy
Range("E210").Select
ActiveSheet.Paste

Selection.Replace What:="90Q1", Replacement:="Feb-90", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

And like that for each quarter...

Thanks for all your help

Gara
 
D

Dave Peterson

Didn't you have trouble when you changed:

03Q1 to Feb-03?

When I did it, the cell showed: 03-Feb, but the date in the formula bar was:
February 3, 2004.

I think I'd do something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim myYear As Long
Dim myMonth As Long
Dim myDay As Long

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
If LCase(.Value) Like "##q#" Then
myYear = CLng(Left(.Value, 2))
If myYear < 70 Then
myYear = 2000 + myYear
Else
myYear = 1900 + myYear
End If
myMonth = (CLng(Right(.Value, 1)) - 1) * 3 + 2
myDay = 1
.Value = DateSerial(myYear, myMonth, myDay)
.NumberFormat = "MMM-YY"
End If
End With
Next myCell

End Sub

or using edit|replaces:

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myRng As Range
Dim iCtr As Long
Dim yCtr As Long
Dim qCtr As Long
Dim BeginYear As Long
Dim EndYear As Long
Dim TotalEntries As Long
Dim myFromStrings() As String
Dim myToValues() As Long

Set myRng = Selection

BeginYear = 1990
EndYear = 2010

'1990 to 2010 is 21 years * 4 qtrs per year = 84
TotalEntries = (EndYear - BeginYear + 1) * 4
ReDim myFromStrings(1 To TotalEntries)
ReDim myToValues(1 To TotalEntries)

iCtr = 0
For yCtr = 1990 To 2010
For qCtr = 1 To 4
iCtr = iCtr + 1
myFromStrings(iCtr) = Right(CStr(yCtr), 2) & "Q" & CStr(qCtr)
myToValues(iCtr) = DateSerial(yCtr, ((qCtr - 1) * 3) + 2, 1)
Next qCtr
Next yCtr

With myRng
.NumberFormat = "mmm-yy"
For iCtr = LBound(myFromStrings) To UBound(myFromStrings)
.Replace What:=myFromStrings(iCtr), _
Replacement:=myToValues(iCtr), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next iCtr
End With

End Sub
 
Top