Converting date from cyymmdd to dd/mm/yy and sorting pivot table

[steven knight]

Dear All,

I am pulling in data from an AS400 which brings in the date field in
native cyymmdd format.

I want to sort the pivot table in date order sequence but show the
date as dd/mm/yy.

I tried pulling a dd/mm/yy formated date from the AS400 and sorting on
this but the sort order did not work correctly for some reason, so i
guess sorting in cyymmdd order is the only way ?

Many thanks for any advice

Regards

 

[Frank Kabel]

Hi
one idea: if you import the dates as dd/mm/yyyy they're
probably stored not as date but as text values in Excel.
Try the following after the import:
- select an empty cell and copy it
- select your imported dates
- goto 'Edit - Paste Specia' and choose 'Add'

now try sorting again

 

[Dave Peterson]

After you have the data (single column???) in excel, you can insert a column to
the right (don't want to overwrite existing data!).

Then select your column and do Data|Text to columns.

You can specify fixed width and even choose to skip that century character. But
make sure you use Data ymd for the date portion.

(If you skip the century character, then you wouldn't need the additional
column--just overwrite your existing data.)

If you mess it up, remember Undo (or close without saving) or even reimport!