How do I conver 3/15/02 into a pivot value that EXLUDES the day, keeps MM/YY?

  • Thread starter Thread starter John Jones
  • Start date Start date
J

John Jones

Trying to take date values in a spreadsheet in this
setup...

03/15/03
03/15/03
03/16/03
03/17/03
03/17/03

But my issue is that when I read these values into a pivot
table, I need the PIVOT table to IGNORE the day value (ie,
15 , 16 , 17) and pick up only the Date values as a date.
In other words, regardless of the specific day of the
month, I want the values associated with the dates all
going into March, 2003. Help, it's driving me nuts!
 
I believe you have to add a help column to your table,
assume the dates start in A2 going down, insert for
instance a new column B, call it Months, in B2 enter

=MONTH(A2)

copy down as long as needed, now apply the Pivot table,
in the layout drag for instance Months to the Row,
then you can select for instance 3 for March and you'll
get the count/sum etc for that month


Regards,

Peo Sjoblom
 
You can change the date field to appear as mth/year by
highlighting the dates in the Pivot Table, right click,
format cells, Choose Custom on the Number tab and type:
mm/yr in the Custom box.

Good Luck!
 
Back
Top