Display mmddyy date as mmyy in pivot tables

[anationalacrobat]

Found myself an interesting problem here. So, I have a date 3-1-09. I only want it to appear as 3-09. Well, simple enough with the "format cell" date option. The value in the cell may still be 3-1 but it appears as 3-09, problem solved, right? Not so.

The people doing the data entry on this sheet are using the real date for the data entered because they think they might care about the day, even though for now they are not. So what does this do on my pivot table reports? Even though the dates are displayed as 3-09, they're actually 3-15-09, 3-1-09, etc, and I get a new entry for every unique date. So if I have five unique dates in the month, the pivot table shows 3-09 five times.

The only way around this that springs to mind seems rather convoluted -- put a second column next to the date and manually construct a string that asks for the month and year and puts them together. As I said, this seems convoluted. Is there a simpler, smarter solution I'm overlooking?

 

[SkipVought]

Hi,

The people doing the data entry on this sheet are using the real date ...

...as well they should, ALWAYS! You REALLY do not want to use anything but REAL DATES for dates.

Check out the Group & Show Detail... in the PT for that Date Field. You can group by Month & Year.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lilliabeth]

You can even group by fiscal quarter. One of the many, many reasons that data entry people are 100% correct for entering real dates.

--Lilliabeth

 

[SkipVought]

You can even group by fiscal quarter

... or week or fortnight, or 28 day lunar month.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}