I have a legacy database which imports data from an excel spreadsheet that is generated by another program. Recently my company updated the program to support upgrading to Windows 10. One of the fields in the spreadsheet now lists the date in the format MMM DD, YYYY, TT:TT AM/PM. Since the field contains that second comma Excel doesn't recognize the date and therefore won't allow me to convert it to a different format (MM/DD/YY, TT:TT AM/PM). If I go in and manually delete the comma then Excel will change the format. This would require me to manually change almost 30,000 cells weekly. Access 2013 doesn't recognize the date either due to that comment, and when I go to import the data it leaves the fields empty. What I'm looking for is a way to import the data as a text field into Access (which I know how to do) and then use a query/macro/vba to change the format into a date the program recognizes. I have tried to make a vba program to remove the commas, however, it doesn't recognize the date without a comma. And my experience/knowledge about programing Access is quite limited. To make things worse I work at a job that doesn't allow access to the internet (for help) and the data is contained on a private server. Does anyone know the best/most efficient way to solve my problem?
Thanks