How can i tell a macro to look for dates (in format dd/mm/yy) in column X and
change it to mmm-yy (oct 07) format? i did this via format cells but as i am
using a pivot table afterwards the pivot table still some how picks the date
up in its original form as opposed to the new form. so a macro would prob do
the job better.

thanks.

Zak, format the entire column as "mmm-yy" and the Pivot Table should display
correctly.
--
Charles Chickering

"A good example is twice the value of good advice."


"Zak" wrote:

> How can i tell a macro to look for dates (in format dd/mm/yy) in column X and
> change it to mmm-yy (oct 07) format? i did this via format cells but as i am
> using a pivot table afterwards the pivot table still some how picks the date
> up in its original form as opposed to the new form. so a macro would prob do
> the job better.
>
> thanks.

Excel stores dates as a number and then formats that number to look like a
date. By changing the format in the spreadsheet you do not change the
underlying number. So when you create your pivot table it does not pick up
the format it just picks up the number. You need to format the date in the
pivot table. Right click the date and select Field Settings -> Number... and
format the date as necessary.
--
HTH...

Jim Thomlinson


"Zak" wrote:

> How can i tell a macro to look for dates (in format dd/mm/yy) in column X and
> change it to mmm-yy (oct 07) format? i did this via format cells but as i am
> using a pivot table afterwards the pivot table still some how picks the date
> up in its original form as opposed to the new form. so a macro would prob do
> the job better.
>
> thanks.

Thanks a million, worked fine.


"Jim Thomlinson" wrote:

> Excel stores dates as a number and then formats that number to look like a
> date. By changing the format in the spreadsheet you do not change the
> underlying number. So when you create your pivot table it does not pick up
> the format it just picks up the number. You need to format the date in the
> pivot table. Right click the date and select Field Settings -> Number... and
> format the date as necessary.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Zak" wrote:
>
> > How can i tell a macro to look for dates (in format dd/mm/yy) in column X and
> > change it to mmm-yy (oct 07) format? i did this via format cells but as i am
> > using a pivot table afterwards the pivot table still some how picks the date
> > up in its original form as opposed to the new form. so a macro would prob do
> > the job better.
> >
> > thanks.