Unable to change date format on downloaded spreadsheet

[spr1nger]

I ran a report from MSDSonline to get an inventory of my locations products. The report has a date format of 1996-03-01. I want to change it to 03/01/1996, (assuming that the 03 is March). Nothing I have tried will change the date format in the column. I've ensured that the cells are not locked. I've "cleared" any formatting on the entire spreadsheet. I've tried to copy/paste values. I'm at a loss. I did a little research on Microsoft's site, and found people posting about the same situation, but could not find a resolution. Can anyone help?

 

[JamesCanale]

Are the dates stored as text? Try adding zero to the whole column of dates in another column. If that works for you, paste that back into your existing column.

 

[spr1nger]

Are the dates stored as text? Try adding zero to the whole column of dates in another column. If that works for you, paste that back into your existing column.

The dates were formatted as "General" when first opened. I'm not sure what you mean by adding a zero in another column. Do you mean put a zero in an empty column, then copy the dates & past them in the column with the zeros? If so, that did not work.

 

[spr1nger]

Does anyone know a formula that will refer to the date in one column & populate it using a different format in another column? I don't want to have to retype a ton of dates manually.

 

[Fluff]

Select the columns & on the data tab select Text To Columns, Delimited, next, clear all checkboxes, next, select YMD from the date dropdown & Finish.

 

[spr1nger]

Select the columns & on the data tab select Text To Columns, Delimited, next, clear all checkboxes, next, select YMD from the date dropdown & Finish.

That worked!!! Thanks so much

 

[Fluff]

Glad we could help & thanks for the feedback.