Need help to correct date format

[Excelski]

Hello All,

I have inherited a sheet with thousands of dates all entered incorrectly, i.e. 21.01.80
What is the best way to convert them so excel can read them?

Thanks

Kevin

[Glenn Kennedy]

Try this first:

=SUBSTITUTE(A1,".","/")+0

then copy/"paste values" and delete your original column.


Otherwise, will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Glenn Kennedy]

Data/Text to columns/delimited/Tab/Date works too and is easier for a one-off.

[Excelski]

Please see attached, thanks for looking

Kevin

[Excelski]

Sorry Glenn, I didn't see your solution the first time i looked?
it works great Thank!

Kevin

[avk]

In another way...
Select range > Ctrl F > In Find What enter .
In Replace with enter /
Click Replace All
Format as "Date"

[Excelski]

Thank you avk.