Analyzing an Array

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

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
DetroitDavid,

So that we can get it right the first time, can we have a screenshot of what the results should look like?
 
Upvote 0
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
BC
2'09211949Easy, this is Sep 21 1949
3'09291936Easy
4'12041925Is this Dec 4 1925 or is it April 12 1925?
5'12261929Easy
6'06261937Easy
7'12211931Easy
8'09251939Easy
9'03101930Mar 10 or Oct 3?
1019061911Stumped - June 19 1911 or Nov 19 1906?
Sheet1
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
1DOB
29/21/1949
39/29/1936
412/4/1925
512/26/1929
CAHPS_06_2013 (43)
 
Upvote 0
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?
 
Upvote 0
Excel Workbook
LAB
1DOBDOB-Date
2'1008192410/08/1924
3'0812192708/12/1927
4'0227194402/27/1944
5'0124193101/24/1931
6'1010193110/10/1931
7'0802193508/02/1935
8'0826196008/26/1960
Sheet1
 
Upvote 0
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?
 
Upvote 0
Sorry, BB - only because it's the first empty column. From there I will copy paste special values back over L2. Thank you!
 
Upvote 0
DetroitDavid,

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


Excel 2007
BEBFBGBHBIBJBKBL
10mmddyyyymmddyyyyyyyymmddmmddyyyyddmmyyyymmddyyyy
11LBBLBBLBB
12DOBDOB-DateDOBDOB-DateDOBDOB-Date
131008192410/08/19241924100810/08/19240810192410/08/1924
140812192708/12/19271927081208/12/19271208192708/12/1927
150227194402/27/19441944022702/27/19442702194402/27/1944
160124193101/24/19311931012401/24/19312401193101/24/1931
171010193110/10/19311931101010/10/19311010193110/10/1931
180802193508/02/19351935080208/02/19350208193508/02/1935
190826196008/26/19601960082608/26/19602608196008/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).

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top