csv import - column not importing as date

[jewel56]

hey there

I am just learning to manipulate data better so would appreciate some help please

1 column imports like a date field (format is general) 08/01/1988

this date is actually 01/08/1988

if I change it to a date format all that happens is either the 0 gets dropped off or it stays.

How do I reverse the dates please

thanks

[jasoncw]

It looks like the month and day are switched. Use Format > Cells, then select Custom in the Number tab. Look in the Type box, and if it is formatted as m/d/yyyy, change to d/m/yyyy, or vice versa.

HTH

Jason

[jewel56]

thanks Jason

my format for the column is date dd/mm/yyyy

when I go to format \ custom and I verify above it does not seem to save it

eg when I go back in to have a look I am back at format = date

very strange! any other suggestions

cheers

[jasoncw]

Are you saving as a CSV or XLS file after changing it? I believe the custom format needs to be saved as an XLS file to be preserved. If this is not possible for your application, I don't know of an answer. Maybe someone else can help you out.

[mudraker]

try this formula in a blank column - change A1 to suit

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))
autofill formula down
copy column with the formula > Pastespecial. values in into column with dates > delete colum with formulas

[jewel56]

ok thanks guys

definately saved as .xls

ok I think I am missing something

I changed my date column to A and inserted a column B (was I supposed to insert the formula into B - relate it to column A

therefore your formula is relating correctly

result I get is #Value!

thanks

[mudraker]

can you post a copy of your book

If neccessary blank all columns & leave date column

[daddylonglegs]

Perhaps you have some extra spaces in the data.

One easy method to try. Select column of dates

Data > Text to Columns > Next > Next > under "column data format" select "date" and MDY > Finish

[jewel56]

thank you to all that replied for me

DaddyLongLegs - excellent that did the trick - much appreciated

saved a lot of work