how to group time field in pivot table

[zhurong]

hi!
My pivot table has a date field,contain data such as
"2008-6-1","2008-6-5","2008-6-7","2008-6-10", etc., how can I group time
field in pivot table so I can display data group by week or month or by
year?
Any help will be appreciated!

Richard.S.Zhu

 

[Mike H.]

I'd add some calculations to my original data that contain the date breaks
the way you want them. For example, if the date is in col a, in b1 have this
formula:

=YEAR(A1) & RIGHT("0" &MONTH(A1),2)
The above formula gives you 200806 for June, 2008. Put the year first and
it sorts better.

 

[zhurong]

But how can I sort in week?
For example: there are rows with 6-8's data, the next is 6-9's data,...
6-14's data, how can I group the data by week(6-8 to 6-14 is the same week)?

 

[zhurong]

Any other options like the follow :

ActiveSheet.PivotTables(PivotName).PivotFields("payday").DataRange.Rows(1).Group
_
Start:=True, End:=True, periods:=Array(False, False, False,
False, True, False, False)

 

[Mike H.]

You have to define your weeks. I'd create another tab, and make it hidden if
you don't want others to get to it. In it, I'd have colums like this:

Start DT EndDT Week
01/01/2008 01/07/2008 1
01/08/2008 01/14/2008 2
etc, etc.

Then I'd do a lookup to get the week # as follows:
Say your date is in A1:
=VLOOKUP(A1,CoCal!A:C,3,TRUE)

The above formula would return the week # from the CoCal tab columns A-C as
shown above.

 

[Mike H.]

I have no idea what your pivot table activity below is doing. Sorry I can't
help.

 

[zhurong]

I found some code as follows ,but I can't catch the meaning with the code
marked *
It seems excel's pivot table also can group by date, but I don't know how.

Dim Rng1 As Range
Dim PivotName As String
PivotName = "pivot1"
Set Rng1 = Selection.CurrentRegion

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Rng1, TableDestination:="R5C7", TableName:=PivotName

ActiveSheet.PivotTables(PivotName).AddFields RowFields:=Array("payday",
"pay_com", _
"pay_no")
ActiveSheet.PivotTables(PivotName).PivotFields("pay_am").Orientation =
xlDataField

*
ActiveSheet.PivotTables(PivotName).PivotFields("pay_day").DataRange.Rows(1).Group
_
Start:=True, End:=True, periods:=Array(False, False, False,
False, True, False, False)

ActiveSheet.PivotTables(PivotName).PivotSelect "total", xlDataAndLabel
Selection.NumberFormatLocal = "#,##0.00"

 

[zhurong]

Thx for ur help!

I have no idea what your pivot table activity below is doing. Sorry I
can't
help.