Result of date as Month-2 digit Year

L

Linda

I have columns of dates that the users must enter as actual dates. I can
format to display month and two-digit year...but when creating pivot tables
it reconizes the entire date. Since I want to summarize only by month and
year or by year only, is there a formula or statement that will change the
date to month-year or year status only?

Thanks.
 
H

Harald Staff

Hi Linda

Sure. Rightclick the date field header in the Pivot table, choose menu
"Group and show detail" > "Group"... . You will get a list offering Seconds,
minutes, hours, days, months, quarters, years. Try selecting months,
quarters and years and see what happens.

Re your other post; Try the Group menu also for other data types, it can
create some amazing summaries.

HTH. Best wishes Harald
 
L

Linda

Thanks Harald...I did try that and the result for date and all other field
headers in the pivot table was....Can Not Group by that Field.

However, I did find that by selecting the rows of the pivot table results
that I want grouped and following the steps you also listed, that they will
group with a title of group 1, group 2 ect. Then it is just a matter of
changing that resulting title to...in my date question of the month-year or
year.

Probably not the best solution...but it works.

Linda

Harald Staff said:
Hi Linda

Sure. Rightclick the date field header in the Pivot table, choose menu
"Group and show detail" > "Group"... . You will get a list offering Seconds,
minutes, hours, days, months, quarters, years. Try selecting months,
quarters and years and see what happens.

Re your other post; Try the Group menu also for other data types, it can
create some amazing summaries.

HTH. Best wishes Harald
Click to expand...
 
H

Harald Staff

Hi Linda

Very very strange. You should test it on a new, small file so that you can
see what it's supposed to do and maybe spot the error from there:

Enter a date in A2 and a number in B2. Fill down 100 rows or so. Add
headers in Row 1, make a pivot table with the dates as row source and group
by the it as described.

Best wishes Harald
 
Top