Excel Question #46

[Kevy Baby]

I have a spreadsheet that can only be downloaded as a tab delimited file. One of the fields is a date/time field that comes in as text in this format:

26-Jun-2014 10:11:13 AM PDT

Obviously I cannot just format it as a date. The shortest way I have found to get this converted to a date is (assuming the source data is in column A):

1. Enter this function in column B: =LEFT(A2,11) (the date portion is always 11 characters)
2. Enter this function in column C: =DATEVALUE(B2)
3. Copy then Paste Value of Column C

Yes, I will combine the two functions into one: I left them separate to make the example easier to follow. Also, there are several hundred rows of data and the formula will need to be filled down.

So
1. Is there a simpler way to do this in Excel? I need to be able to sort/group/etc. the data by dates and other data in the spreadsheet (daily summary of debits and credits by company name in a monthly report)
2. Having never worked in Macros (but always willing to learn something new) how difficult is this to script so that the CSV file can be automatically processed once per month? I want to be able to hand off to our accounting department to handle (I could manually do this every month myself in probably less time than I would spend writing and testing said macro, but I would rather automate and give to someone else to handle).

And I cannot change the report as it is provided: I gotta work with what I have.

[Moonliner]

The basic formula looks good: =DATEVALUE(LEFT(A1,11))

I would read Tab delimited data into "sheet2" and then use the formula on "sheet1" to format and display it the way the users need.

Does that help at all?