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