Date formats

[Guest]

I have an csv file which i have imported into excel from our banking software.

The date has been imported as 180406 being 18 april 2006

if i try to format this into a better format i get a completely diferent
date. Is there an easy way to format this as i have 500 rows of transactions
i need to tidy up?

Thank you in advance
Hervinder

 

[Gary Brown]

Assuming the date is in the following format:-

DDMMYY

you will need to put a formula, in to convert the data into a date.

I have assumed that all dates are in the current century, the formula I
would use would be:-

=DATE(20&RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

Where A1 contains a date.

If the day doesn't have a leading zero, you will need to adjust the
above formula to accomodate. If you need help let me know.

G

 

[Guest]

In a helper column, try:

=DATE(RIGHT(A2,2)+2000,MID(A2,3,2),LEFT(A2,2))

and copy down.

If OK, Copy and paste special==>values to yoor original column

HTH

 

[Dave Peterson]

If it's a single column, you can select that column.
Data|text to columns
choose fixed width (but remove any separators that excel guessed)
Choose dmy as the field type.

If you rename the .csv file to .txt, you can open the .txt file and do this work
when you're importing the file.