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

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


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534092

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

"Hervinder" wrote:

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

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.

Hervinder wrote:

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

--

Dave Peterson