counting records by month and year / date format issues

[Barbara Sabatino]

I have data I've exported that is in format mm/dd/yyyy. I'm trying to
pivot to count the number of records per mmm-yyyy. Is there a way to
change or convert the data of the date--not just the format--to dd-mmm-
yyyy such that I can then add a formula to pull the middle and end
(using MID function) to just give me the mmm-yyyy for my pivot? Or an
esier way if anyone knows that achieves the same endpoint would be
greatly appreciated.

 

[James Ravenswood]

I have data I've exported that is in format mm/dd/yyyy.  I'm trying to
pivot to count the number of records per mmm-yyyy.  Is there a way to
change or convert the data of the date--not just the format--to dd-mmm-
yyyy such that I can then add a formula to pull the middle and end
(using MID function) to just give me the mmm-yyyy for my pivot?  Or an
esier way if anyone knows that achieves the same endpoint would be
greatly appreciated.

Hi Barbara:

If you have dates in column A like:

2/1/2006
12/13/2007
6/18/2007
1/8/2002
9/5/2005
12/8/2000
4/6/2008
3/19/2004
6/17/2006
6/11/2002
5/18/2001
7/17/2004
4/5/2001
12/5/2009
12/7/2009
12/14/2000
7/19/2006
6/18/2003
8/7/2010
11/4/2004

then in B1 enter:

=YEAR(A1) &TEXT( MONTH(A1),"00") and copy down to see:

2/1/2006 200602
12/13/2007 200712
6/18/2007 200706
1/8/2002 200201
9/5/2005 200509
12/8/2000 200012
4/6/2008 200804
3/19/2004 200403
6/17/2006 200606
6/11/2002 200206
5/18/2001 200105
7/17/2004 200407
4/5/2001 200104
12/5/2009 200912
12/7/2009 200912
12/14/2000 200012
7/19/2006 200607
6/18/2003 200306
8/7/2010 201008
11/4/2004 200411

You can pivot by the column B values