Extracting Date from Text String

ashoke — Thu, 31 May 2012 19:26:00 GMT

I get Excel files from web submission data exchange with date&time stamp as file names. The length of the file names is always 24 characters long with first part being date, second part time with AM and PM and then filler, connected with “_”. In regards to date extraction, it gives me four different types. I need to input the file name in filed1 and read the date in field2 automatically as expected below. Please note that I want field2 as date to run future queries.

Type	Field1	Note	Expected Field2	Note
1	3_8_2012_10_53_56_AMxxxx	Both day and month fields are single digit long	3/8/2012	Date format
2	4_12_2012_1_48_55_PMxxxx	Day field is single digit but month field is double digits long	4/12/2012	Date format
3.	12_6_2012_2_12_20_PMxxxx	Day field is double digits but month field is single digit long	12/6/2012	Date format
4	11_12_2012_1_29_53_PMxxx	Both day and month fields are double digits long	12/12/2012	Date format

Apparently, it seems very simple to read date. But I cannot figure out any logic to read field1 in filed2 and make it date format.

Any help would be much appreciated.

Thanks,

Ash

General

Extracting Date from Text String