Excel Experts PLEASE HELP ASAP

[clubby]

Hi guys

I have a little problem on my hand...
I have a CSV file which the date field is causing me heaps of problems...

I have a about 30,000 records and one of the columns is a date format...
My problem is with this column,
some of the dates are in the format of
"DD-MM-YYYY HH:MM:SS AM/PM"
and the other format is in "DD-MM-YY"

I have tried to format the cells so as they are all in the same format, that is "DD-MM-YYYY".

No matter what i do I still can not format them to be all the same...I tried using the Wizard in format cell and using the standard DATE, TIME, and I cant seem to get in the same format I have even tried the customs and no luck...

Is this possible and does anyone out there can assist me with this problem ...Your help is much appreciated...

 

[xlhelp]

Hi!

Highlight all those cells. Go to Edit, Clear, Formats. Apply the date format again.

Hope that clears up the problem.

Indu Getting married was the most expensive way for me to find out all my faults.

 

[ETID]

If your dates are in col A, then try this formula in a new column....then fill down.

=TEXT(MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1),"mm/dd/yyyy&quot


if successfull, then copy and (paste special>values only)
over top of your origional dates.

 

[AWithers]

I have had similar problems before and think the problem lies with excel not recongising all the entries as dates and thus it cant format them as you wish. If you click in the cell what comes up in the formula bar?

How I got over it was to search and replace on some of the character in the string e.g. replace '/' with '/' or '-' with '-' or ':' with ':' this effectively re-enters the data and excel should decide they are dates and then you can folrmat them as you want.

Andy