Excel 2007 Database Date Formatting

Question

I have a list of 568 dates in a excel table.

I read the first few dates as mm/dd/yyyy as follows,

1/2/2009 (Jan 2)
1/2/2009 (Jan 2)
1/16/2009 (Jan 16)
2/1/2009 (Feb 6)

When I select format cells the computer converts to dd/mm/yyyy as follows as rewrites as,

2/1/2009 (Feb 1)    << Switches date and month.
2/1/2009 (Feb 1)    << Switches date and month.
1/16/2009 (Jan 16)  << Leaves in place because it cant convert 16 to a month
1/2/2009 (Jan 2)    << Switches date and month.

I tried to copy and past special-values into pre-formated cells but the problem persists. The cells cant be formatted prior to this action because it converts all the value. Also tried SQL formatting update which did not work.

Note: This is a spread sheet coming from a mac computer.

Note: If I type the date into the field it gives me the correct format. But if I cut and past the date, it gives me the old cell format that I cant seem to change, even if I past special value.

I plan to retype the dates manually but I rather not because this list is already perfect and I think this excel program should be able to fix this. Any help would be great.

I need the program to think of these dates as they are already listed... as mm/dd/yyyy with no Strings attached!

What is your locale in the control panel under Regional and Language Options? The order or the date is set there. — Fionnuala
There is other software depending on my computers date formatting so I prefer not to change it. Need to know how to set these dates as MM/DD/YYYY — chris999999999

Fionnuala Fionnuala · Accepted Answer · 2011-10-20T20:25:22

Format 568 dates to your custom format of mm/dd/yyyy, next copy the cells, then choose paste special->values to paste them into the pre-formatted cells.

Excel 2007 Database Date Formatting

1 Answers