Display dates as months
 

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.

Display dates as months
 

One way is to use DataText 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 DataText 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KK" wrote:
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.

Display dates as months
 

Select your range Custom Format mmm-yy

"KK" wrote:

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.

Display dates as months
 

On Thu, 19 Apr 2007 19:24:03 -0700, KK wrote:

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