DetroitDavid
Board Regular
- Joined
- Jul 20, 2013
- Messages
- 211
I need to analyze an array to determine position of the three date components: MM, DD, & YYYY
Note: Array will be a text string formatted with a ‘ to break out of date format, and consistent in its MM DD YYYY placement. It will already have exactly 8 digits, with leading zeros for dates and months less than 10, and all separators will be stripped out.
Something like...
Analyze array
If num 5-6 are either 19 or 20 Then ''' assume Year at end
If num 1-2 (of all records in array) are <= 12 Then ''' assume MMDDYYYY format
sDateSeq = "MDY"
Else If num 3-4 (of all records in array) are <= 12 Then ''' assume DDMMYYYY format
sDateSeq = "DMY"
Else ''' unknown
sDateSeq = Msg(“Not enough dates to analyze.”)
End
Else ''' assume year at beginning - format is either YYYYMMDD or YYYYDDMM
''' Flip first and last 4 digits and re-run
End
Thank you!
DD
Note: Array will be a text string formatted with a ‘ to break out of date format, and consistent in its MM DD YYYY placement. It will already have exactly 8 digits, with leading zeros for dates and months less than 10, and all separators will be stripped out.
Something like...
Analyze array
If num 5-6 are either 19 or 20 Then ''' assume Year at end
If num 1-2 (of all records in array) are <= 12 Then ''' assume MMDDYYYY format
sDateSeq = "MDY"
Else If num 3-4 (of all records in array) are <= 12 Then ''' assume DDMMYYYY format
sDateSeq = "DMY"
Else ''' unknown
sDateSeq = Msg(“Not enough dates to analyze.”)
End
Else ''' assume year at beginning - format is either YYYYMMDD or YYYYDDMM
''' Flip first and last 4 digits and re-run
End
Thank you!
DD
Excel Workbook | |||
---|---|---|---|
A | |||
1 | Array | ||
2 | '09211949 | ||
3 | '09291936 | ||
4 | '12041925 | ||
5 | '12261929 | ||
6 | '06261937 | ||
7 | '12211931 | ||
8 | '09251939 | ||
9 | '03101930 | ||
10 | '11131936 | ||
Sheet3 |