Analyzing an Array

[DetroitDavid]

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

Excel Workbook
	A
1	Array
2	'09211949
3	'09291936
4	'12041925
5	'12261929
6	'06261937
7	'12211931
8	'09251939
9	'03101930
10	'11131936
Sheet3

 

[hiker95]

DetroitDavid,

So that we can get it right the first time, can we have a screenshot of what the results should look like?

 

[DetroitDavid]

So that we can get it right the first time, can we have a screenshot of what the results should look like?

OK, hope this helps...

The only way to definitively tell which 4 digits are a year is to look at the whole set.
Fortunately the entire dataset will be in 1 format.
In this case, the last one does not fit, but you get the idea.
The only way to definitively tell which 2 digits are the month and which 2 the date is to look at the whole set.
So, I can't programmatically look at 1 date and make a decision.
I need to logically look at the set and have Excel tell me which is which.

Ultimately I will convert it to MM/DD/YYYY, but I have 140 incoming data sources.

Excel Workbook
	B	C
2	'09211949	Easy, this is Sep 21 1949
3	'09291936	Easy
4	'12041925	Is this Dec 4 1925 or is it April 12 1925?
5	'12261929	Easy
6	'06261937	Easy
7	'12211931	Easy
8	'09251939	Easy
9	'03101930	Mar 10 or Oct 3?
10	19061911	Stumped - June 19 1911 or Nov 19 1906?
Sheet1

 

[hiker95]

DetroitDavid,

I have some code that will do what you originally requested, and, it will replace the original array data in the format MMDDYYYY.

Let me add some extra code to check for year in the first 4 digits.

You have displayed two screenshots of different worksheet names. I will adjust the macro to run on the active worksheet.

1. What column in the active worksheet will the array be in?
2. What cell in the above column do the actual dates begin?

Ultimately I will convert it to MM/DD/YYYY

3. Would you like the results of the macro to display the array like MM/DD/YYYY?

 

[DetroitDavid]

1. What column in the active worksheet will the array be in?
2. What cell in the above column do the actual dates begin?
3. Would you like the results of the macro to display the array like MM/DD/YYYY?

1) The DOB is originally in col I but will end up in col L. I am moving several things around and at this point in my code I’ve moved it to col L.
2) Dates and all imported data begin in row 2 (the header is a single row).
3) Yes, the final goal is to have a MM/DD/YYYY format with preceding zeros as needed so all strings contain exactly 10 digits (with the /’s).
So at this point I want to look at the dataset L2:Lx, determine what the numbers mean (in what place are MM DD & YYYY, rearrange, separate with “/” and paste special as a date back into its same place. But I’m open to placing it somewhere else for verification and testing. My workbook has data through col BA, so beginning with col BB it’s open.

Thank you!

Excel Workbook
	L
1	DOB
2	9/21/1949
3	9/29/1936
4	12/4/1925
5	12/26/1929
CAHPS_06_2013 (43)

 

[hiker95]

DetroitDavid,

Can I have a screenshot of the raw data in column L?

And, can I have a screenshot of the results (manually formatted by you) in column BB?

 

[DetroitDavid]

Excel Workbook
	L	AB
1	DOB	DOB-Date
2	'10081924	10/08/1924
3	'08121927	08/12/1927
4	'02271944	02/27/1944
5	'01241931	01/24/1931
6	'10101931	10/10/1931
7	'08021935	08/02/1935
8	'08261960	08/26/1960
Sheet1

 

[hiker95]

DetroitDavid,

My workbook has data through col BA, so beginning with col BB it’s open.

Raw data in column L beginning in cell L2 is good.

The output will be in which column?
1. BB?
or
2. AB?

 

[DetroitDavid]

Sorry, BB - only because it's the first empty column. From there I will copy paste special values back over L2. Thank you!

 

[hiker95]

DetroitDavid,

The below table contains three column L's raw data re-arranged, and, column BB all the same results.

Excel 2007
	BE	BF	BG	BH	BI	BJ	BK	BL
10	mmddyyyy	mmddyyyy		yyyymmdd	mmddyyyy		ddmmyyyy	mmddyyyy
11	L	BB		L	BB		L	BB
12	DOB	DOB-Date		DOB	DOB-Date		DOB	DOB-Date
13	10081924	10/08/1924		19241008	10/08/1924		08101924	10/08/1924
14	08121927	08/12/1927		19270812	08/12/1927		12081927	08/12/1927
15	02271944	02/27/1944		19440227	02/27/1944		27021944	02/27/1944
16	01241931	01/24/1931		19310124	01/24/1931		24011931	01/24/1931
17	10101931	10/10/1931		19311010	10/10/1931		10101931	10/10/1931
18	08021935	08/02/1935		19350802	08/02/1935		02081935	08/02/1935
19	08261960	08/26/1960		19600826	08/26/1960		26081960	08/26/1960
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub MMDDYYYY()
' hiker95, 08/02/2013
' http://www.mrexcel.com/forum/excel-questions/717293-analyzing-array.html
Dim a As Variant, i As Long, n As Long, h As String
With ActiveSheet
  a = .Range("L2:L" & .Range("L" & Rows.Count).End(xlUp).Row)
  n = 0
  For i = 1 To UBound(a, 1)
    If Mid(a(i, 1), 5, 2) = 19 Or Mid(a(i, 1), 5, 2) = 20 Then
      n = n + 1
    End If
  Next i
  If n <> UBound(a, 1) Then
    For i = 1 To UBound(a, 1)
      h = a(i, 1)
      a(i, 1) = Mid(h, 5, 4) & Mid(h, 1, 4)
    Next i
  End If
  n = 0
  For i = 1 To UBound(a, 1)
    If Mid(a(i, 1), 1, 2) <= 12 Then
      n = n + 1
    End If
  Next i
  If n <> UBound(a, 1) Then
    For i = 1 To UBound(a, 1)
      h = a(i, 1)
      a(i, 1) = Mid(h, 3, 2) & Mid(h, 1, 2) & Mid(h, 5, 4)
    Next i
  End If
  For i = 1 To UBound(a, 1)
    h = a(i, 1)
    a(i, 1) = Mid(h, 1, 2) & "/" & Mid(h, 3, 2) & "/" & Mid(h, 5, 4)
  Next i
  .Range("BB2").Resize(UBound(a, 1)).NumberFormat = "@"
  .Range("BB2").Resize(UBound(a, 1)) = a
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MMDDYYYY macro.