Andyatwork
Board Regular
- Joined
- Mar 29, 2010
- Messages
- 94
Hey all,
I would like to collect some suggestions for ways to solve a date extraction issue.
I have two columns, one containing a reference number, the other containing an alphanumeric string of no fixed format (user entered free text with no data validation, yay!) that includes a date.
The date could be at the beginning of the string in yyyymmdd format, or somewhere in the middle or end of the string expressed as dd/mm/yyyy, dd/mm/yy or d/m/yy or any combination thereof with a range of separators; decimal, hyphen, slash etc.
It will only appear in year first format at the beginning of the string.
I need to extract the date from the string (and associate it with the number in the first column) and keep it in UK format.
I am looking for suggestions on how I might be able to achieve this. I have a couple of ideas.
Extra bonus rules to make things really interesting;
My first idea (and fallback plan if parsing out the date with VBA is too difficult and I suspect the date validation will be an utter beast) is to generate a table or user form that lists the numbers in the first column, ignore the second column completely, or display it as reference, and get the users to manually key the associated date. I will then need pointers on creating a dynamically sized table/user form...
The second idea is to chop the string into single character chunks, discard any alpha, retain numerals, recombine the numbers, guess the date based on number of numbers, (ignoring numbers that appeared after an 'x') and then get Users to confirm.
i.e., if there are 8 digits, and they were the first 8 digits then I know it is yyyymmdd.
If there are 8 digits and they were not the first 8 then i can reasonably assume they are ddmmyyyy.
If there are 6 digits then I can reasonable assume they are ddmmyy.
If there are 4 or 5 digits then I can assume the last two are yy and the remainder are either dm, dmm or ddm and maybe puzzle through some testing there but I don't think the development time would be worth it so will probably just give an error message and ask the user to rekey those.
If any of the guessed dates are more than 31 days from system date then I can give another error message as faffing about with further checks will be more trouble than it is worth.
So, any insights to share/pointers to give? Pretty please?
I would like to collect some suggestions for ways to solve a date extraction issue.
I have two columns, one containing a reference number, the other containing an alphanumeric string of no fixed format (user entered free text with no data validation, yay!) that includes a date.
The date could be at the beginning of the string in yyyymmdd format, or somewhere in the middle or end of the string expressed as dd/mm/yyyy, dd/mm/yy or d/m/yy or any combination thereof with a range of separators; decimal, hyphen, slash etc.
It will only appear in year first format at the beginning of the string.
I need to extract the date from the string (and associate it with the number in the first column) and keep it in UK format.
I am looking for suggestions on how I might be able to achieve this. I have a couple of ideas.
Extra bonus rules to make things really interesting;
- The string might contain numbers that are not part of the date; e.g., 30/05/2014 x14, 12.8.14 x6 etc.
- If the date is at the beginning of the string it will definitely be in yyyymmdd as that is system generated and easy to test for.
- All dates will be in spitting distance of the system date, specifically, within a month and not in the future.
My first idea (and fallback plan if parsing out the date with VBA is too difficult and I suspect the date validation will be an utter beast) is to generate a table or user form that lists the numbers in the first column, ignore the second column completely, or display it as reference, and get the users to manually key the associated date. I will then need pointers on creating a dynamically sized table/user form...
The second idea is to chop the string into single character chunks, discard any alpha, retain numerals, recombine the numbers, guess the date based on number of numbers, (ignoring numbers that appeared after an 'x') and then get Users to confirm.
i.e., if there are 8 digits, and they were the first 8 digits then I know it is yyyymmdd.
If there are 8 digits and they were not the first 8 then i can reasonably assume they are ddmmyyyy.
If there are 6 digits then I can reasonable assume they are ddmmyy.
If there are 4 or 5 digits then I can assume the last two are yy and the remainder are either dm, dmm or ddm and maybe puzzle through some testing there but I don't think the development time would be worth it so will probably just give an error message and ask the user to rekey those.
If any of the guessed dates are more than 31 days from system date then I can give another error message as faffing about with further checks will be more trouble than it is worth.
So, any insights to share/pointers to give? Pretty please?
