Date format in pivot table

[adamf]

I am trying to create a simple pivot table that will produce some monthly reports.

The main excel worksheet is used by some users with only basic excel skills, so I want to keep things simple for them. To this end, they enter a date in dd/mm/yyyy format. I have then added a column that replicates the date, but formatted as 'mmm' to only show the month.

I hoped that the pivot table would then allow me to select data for the month (in the page area), but as the data is only formatted as mmm, but still an underlying date, I can't get it to work, so my dropdown field is huge (an otion for every date data was entered)!

Any ideas on how I can either transpose the dd/mm/yyyy field to the month, but formatted as text, or get the pivot table to read jan, feb, mar, apr etc.....?

Adam F
Solaris System Administrator

 

[xlbo]

you can either use the GROUP BY function or you would need to add a column to your dataset which uses the MONTH function on your dates to create your month groupings

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Or you can just use the date field AS IS in the Pivot Table -- right click the date field in the PT and select Grou & Outline>Group

In the Group Window, select Month (and Year if your data spans more than one year)

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]