how do i convert dates to labes eg 01/01/02 to jan 02

G

GIBBON

DATES ARE HELD AS NUMERICS AND FORMATING ALLOWS YOU TO PRESENT THEM AS DATES
HOWEVER WHEN YOU USE PIVOT TABLES AND AND YOU WANT DATES GROUPED AS MONTHS
OR YEARS ITS IMPOSSIBLE AS THE SOURCE DATE ISN'T HELD THAT WAY
 
B

Barb Reinhardt

You could put in a helper column and do something like this

=text(a1,"yyyy") & " - " & text(a1,"mm")

Or
=Date(year(A1),month(A1),1)
 
B

Bernard Liengme

To extend Barb's answer. If you want the Pivot Table to group by months use
a helper column with =MONTH(A1) where a1 is the first date in your dataset

Please do not use CAPITALS; This is considered to be shouting.
best wishes
 
S

ShaneDevenshire

Hi,

Excel's pivot table can group dates by month and year! To do this the dates
must be in the data area as legal Excel dates, no need for helper columns.

1. Suppose you data has Date, Dept and Amount as fields, select the data
2. Choose the command Data, PivotTable and PivotChart Report, click Next twice
3. On the 3rd step of the wizard click Layout
4. Drag the Date field to the Row area, the Dept field to the Column area,
the Amount field to the Data area
5. Click OK, Finish
6. Put your cursor in the Row field (Date) and choose the command
PivotTable, Group and Show Detail, Group
7. In the Grouping dialog box Month will be select, click on Year also (you
could also group by quarter), then click OK.

you've got what you asked for.
 
Top