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

 

[Ron Rosenfeld]

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.

If your dates make up the rows of the pivot table, you can right click/ Group / select months and years.