Display dates as months

[Guest]

Hi,

I have a range of dates such as:
3/19/2003
3/20/2003
3/20/2003
3/20/2003
3/21/2003
3/21/2003
3/21/2003
I need this to be converted in the Mar-03 format and do away with the
individual dates so that I can use it for a pivot table. The above is only an
example as I have 40,000 entries as above all various months.

 

[Guest]

One way is to use Data>Text to cols to convert the source col "dates" to real
dates, then apply a formula in an adjacent col using TEXT for use in the PT

Assuming the source col is col A, data in A1 down

Select the source col A, click Data>Text to Columns
Click Next > Next.
In step 3 of the wiz, check "Date", select MDY from droplist, click Finish
This converts the entire col to real dates.

Then place in say, B1:
=TEXT(A1,"mmm-yy")
Copy down, then freeze the values in col B with an "in-place" copy > paste
special as values. This gives the required format in a col you can use in the
PT (col B) while preserving the source dates col as-is in col A (for other
use as may be desired)

 

[Guest]

Select your range > Custom Format mmm-yy

 

[Ron Rosenfeld]

Hi,

I have a range of dates such as:
3/19/2003
3/20/2003
3/20/2003
3/20/2003
3/21/2003
3/21/2003
3/21/2003
I need this to be converted in the Mar-03 format and do away with the
individual dates so that I can use it for a pivot table. The above is only an
example as I have 40,000 entries as above all various months.

Would not Grouping by months in the Pivot table (or Months and Years)
accomplish the same result more easily?
--ron