MS Excel Problems

[Ozzy]

Hi Folks,
I need some help with an Excel problem I have.

I have a CSV file with lots of records in it and one column has the dates formated as "YYYY-MM-DD HH:MM:SS" in plain text. I have imported this CSV file into Excel, and I must have the dates in the following format "DD/MM/YYYY" (I don't need the time of day, and the times are all 00:00:00 anyway).
Just changing the cell format isn't working so does anyone good on Excel have any other idea's please? Excel isn't seeing the extries as a date/time, but is treating them as standard text input.

Cheers
Richard

 

[Ozzy]

Never mind, fixed it!
I have to go through each cell clicking on F2 and then Enter to get it to recalc the cell !

 

[annethedonn]

is it a read only file? Sometimes they protect the work sheets from changes? I'd need to have a play as I've come across this before and I usually manage to figure it out.

What is a csv file just out of interest?

 

[Ozzy]

Comma Separated Values

A file of information made up 1 line per record as follows
name,address,postcode,telephone,email
anothername,nextaddress,nextpostcode,nextphone,nextemail
and so on.

 

[kyber]

Too late now, but for future reference...

You needed to add another column and use a strring to date function to, er, generate dates from the text strings. Once done you can post the values (not the formulas) over the original column. Use the DATEVALUE function. You can format the new/revised column as you wish.

However, you should have been able to force Excel to read the the csv data correctly in the first place. Use the Data | Import External Data option on the menu to import the data into a blank spreadsheet rather than opening CSV files directly. You can then tell Excel how to understand each field (including selecting the data order (e.g.YMD).

Stuart