Formating a text date

[Allan]

A2007. Default date format is day month year.

I have a text [Time Stamp] field with the data imported from an Excel spreadsheet in the form of "3/21/2012 8:14:52 PM" or "3/12/2012 8:52:11 AM" - the US format. (Attemps to change to a date format in Excel failed too - it seems Excel does not like the time included).

If converted during import from Excel the second example formats as the 3rd of December 12.

Conversion by changing the Data Type in the table to Date/Time after import or with a query recognises the first example as 21 March but leaves the second example as 3 December - in this case a date in the future.

Can anyone tell me how to change the text field contents to a Date/Time field please, accurately reflecting the true date? I have tried many examples/suggestions in this forum. Because the number of digits in the month and day can either be single or double I haven't been able to get the Left, Mid, Right function to work either.

Thanks in anticipation.

 

[Allan]

Problem solved.

I imported the US date formatted text to a Date/Time field with the Format (Field Properties) set to: mm\/dd\/yy h:n:s AM/PM. All dates faithfully converted in double digits and I could use a query to change the to dd mm yyyy h:n:s.