Excel, RegEx and Dates

[Atroxell]

Okay, I am trying to use RegEx for the firs time and I am missing something.

I have files sent to me to include in our Act-On/Salesforce system. Part of the processing is to standardize the data. I have the dates sent to me in the following sample formats. Since I don't want to have to write huge blocks of rules to try to extract the dates rom the values, I thought I would give RegEx a try. The first three columns (A, C, and E) are standard date formats for Excel and I can use isDate() to test those. But the other three are being tricky.

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	Time Stamp		Download Date		Date		Registration Date		Date/Timestamp of Activity		When
2	9/1/2014		2-Sep-14		8/29/14 1:22 PM		08/15/2014 12:08:29 PM PDT		31 Aug 2014 02:00PM		Thursday, Sep 04 2014, 08:02 AM MDT
3	9/2/2014		1-Sep-14		8/29/14 2:30 PM		08/28/2014 07:36:38 PM PDT		20 Aug 2014 03:24PM		Thursday, Aug 28 2014, 01:52 PM MDT
4	9/2/2014		1-Sep-14		8/29/14 5:14 PM		08/22/2014 05:56:08 AM PDT		25 Aug 2014 01:30PM		Thursday, Aug 28 2014, 02:52 PM MDT
5	9/2/2014		31-Aug-14		8/30/14 3:15 AM		08/24/2014 09:43:06 AM PDT		19 Aug 2014 10:37PM		Thursday, Aug 28 2014, 02:41 PM MDT
6	9/2/2014		30-Aug-14		8/30/14 1:51 PM		08/22/2014 07:14:09 AM PDT		19 Aug 2014 08:52PM		Tuesday, Sep 02 2014, 02:58 PM MDT
7	9/2/2014		30-Aug-14		8/30/14 7:16 PM		08/22/2014 05:55:31 AM PDT		20 Aug 2014 09:27AM		Saturday, Aug 30 2014, 03:34 PM MDT
8	9/2/2014		30-Aug-14		8/31/14 8:57 AM		08/16/2014 02:27:11 PM PDT		4 Sep 2014 04:01PM		Sunday, Aug 31 2014, 01:34 AM MDT
9	9/2/2014		29-Aug-14		8/31/14 1:45 PM		08/22/2014 05:51:59 PM PDT		27 Aug 2014 05:04PM		Thursday, Aug 28 2014, 02:07 PM MDT
10	9/2/2014		2-Sep-14		8/31/14 10:55 PM		08/26/2014 12:04:44 PM PDT		24 Aug 2014 08:05AM		Thursday, Aug 28 2014, 07:47 PM MDT
11	9/2/2014		1-Sep-14		9/1/14 8:34 AM		08/19/2014 11:44:35 AM PDT		28 Aug 2014 07:17AM		Friday, Aug 29 2014, 11:18 AM MDT
12	9/2/2014		1-Sep-14		9/1/14 11:02 AM		08/22/2014 07:50:39 AM PDT		4 Sep 2014 04:01PM		Tuesday, Sep 02 2014, 07:10 AM MDT
13	9/2/2014		2-Sep-14		9/1/14 12:17 PM		08/27/2014 01:07:43 PM PDT		31 Aug 2014 04:50PM		Tuesday, Sep 02 2014, 08:43 AM MDT

<tbody>
</tbody>

I have tried the following code to attempt to coax out the patterns, but I am afraid my RegEx knowledge is too limited. The code works with standard dates in columns A & C. But I cannot get the other columns to work. Apparently I am missing something...

Sub newStyle()


      regExPattern(0) = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](20\d\d)"     ' Seems to be VALID
      regExPattern(1) = "([1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.](20\d\d)"          ' Seems to be VALID
      regExPattern(2) = "([1-9]|[12][0-9]|3[01])[-](Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[-](20\d\d)(\s)(0-9)(0-9)(\:)(0-9)(0-9)(AM|PM)(.)"
      regExPattern(3) = "(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday)(\,)(\s)(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\s)" _
                                         & "(0[1-9]|[12][0-9]|3[01])(\s)(20\d\d)(\,)(\s)(0-9)(0-9)(.)(0-9)(0-9)(AM|PM)(.)(AM|PM)(\s)([A-Z][A-Z]\T)"
      regExPattern(4) = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](20\d\d)(\s)(0-9)(0-9)(.)(0-9)(0-9)(AM|PM)(.)([A-Z][A-Z]\T)"
      regExPattern(5) = "'     (20\d\d)[- /.](0[1-9]|1[012])[- /.](20\d\d)"


      For x = 0 To 5
            'chkVal = RegExDate("2-Sep-14", regExPattern(x))
            If RegExDate("2-Sep-14", regExPattern(x)) <> "" Then
                  MsgBox "worked at #" & x
                  Exit For
            End If
      
      Next x
End Sub

Private Function RegExDate(s As String, strPattern As String) As String
    Dim re, match
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = strPattern
    ' re.Pattern = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](20\d\d)"
    re.Global = True


    For Each match In re.Execute(s)
        MsgBox match.Value
        RegExDate = match.Value
        Debug.Print RegExDate
        Exit For
    Next
    Set re = Nothing
End Function

Can someone give me a few pointers to set me on the correct course?

Thanks in Advance.

 

[ukmikeb]

Hi

This thread :-
http://www.mrexcel.com/forum/excel-questions/247848-extract-date-field-unformatted-date.html

will help with parsing dates delimited by "/" or "-" and with 2 or 4 digit years.

hth

 

[Atroxell]

Hi

This thread :-
http://www.mrexcel.com/forum/excel-questions/247848-extract-date-field-unformatted-date.html

will help with parsing dates delimited by "/" or "-" and with 2 or 4 digit years.

hth

Thanks for the response!

That link describes using Excel formats and works great. But it won't do anything for the toehr (bizarre) formats that I encounter. Like the ones in the columns to the left in my example.

Thanks!

 

[István Hirsch]

Here is an attempt to process column G by the use of this UDF. It is supposed that PDT is constant. If the date pattern is correct, it puts OK in the cell otherwise the cell is left blank.

Function DCheck(c As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(^([\d]{2}/){2}[\d]{4} ([\d]{2}:){2}[\d]{2} (AM|PM) PDT)"
        If .Test(c) Then DCheck = .Replace(c, "OK")
    End With
End Function

P.S.: This UDF checks the pattern, but does not check if the values are 'real'.

 

[Atroxell]

Here is an attempt to process column G by the use of this UDF. It is supposed that PDT is constant. If the date pattern is correct, it puts OK in the cell otherwise the cell is left blank.

Function DCheck(c As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(^([\d]{2}/){2}[\d]{4} ([\d]{2}:){2}[\d]{2} (AM|PM) PDT)"
        If .Test(c) Then DCheck = .Replace(c, "OK")
    End With
End Function

P.S.: This UDF checks the pattern, but does not check if the values are 'real'.

Thanks! I'll give this a shot when I get back to work on Monday! I'm keeping my fingers crossed. They won't always be "PDT", but I can probably extrapolate a solution if this works.

 

[ukmikeb]

Thanks for the response!

That link describes using Excel formats and works great. But it won't do anything for the toehr (bizarre) formats that I encounter. Like the ones in the columns to the left in my example.

Thanks!

In post #6 of the quoted thread there is a Regex Pattern for the extraction of dates with "/"s or "-"s.

The following pattern should cater for all date formats :-

.Pattern = (((\d{1,2}(/|-)){2}(\d{4}|\d{2})|((((Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\s)(\d{1,2})|(\d{1,2})(\s|-)(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec))(\s|-))(\d{4}))))"

hth

 

[Peter_SSs]

This udf seems to work for the sample data posted. If you have other data formats for which it doesn't work, could we see samples of them?

Function ExtrDate(s As String)
  Dim bits
  Dim i As Long
  
  ExtrDate = "No date found"
  s = Replace(Replace(s, "am", ",", 1, -1, 1), "pm", ",")
  bits = Split(s, ",")
  For i = 0 To UBound(bits)
    If IsDate(bits(i)) Then
      ExtrDate = Int(CDate(bits(i)))
      Exit Function
    End If
  Next i
End Function

Edit: Correction to this function posted in post #13

 

[István Hirsch]

This udf seems to work for the sample data posted. If you have other data formats for which it doesn't work, could we see samples of them?

Function ExtrDate(s As String)
  Dim bits
  Dim i As Long
  
  ExtrDate = "No date found"
  s = Replace(Replace(s, "am", ",", 1, -1, 1), "pm", ",")
  bits = Split(s, ",")
  For i = 0 To UBound(bits)
    If IsDate(bits(i)) Then
      ExtrDate = Int(CDate(bits(i)))
      Exit Function
    End If
  Next i
End Function

For me (in another date system) Peter’s UDF works correctly (for column G) if:

s = Replace(Replace(s, "am", ",", 1, -1, 1), "pm", ",", 1, -1, 1)

 

[Peter_SSs]

For me (in another date system) Peter’s UDF works correctly (for column G) if:

s = Replace(Replace(s, "am", ",", 1, -1, 1), "pm", ",", 1, -1, 1)

István

Are you saying that it didn't work as I posted?
I thought that having specified the final 3 parameters of the Replace function in the first (inner) Replace, that those parameters would automatically flow/apply to the second (outer) Replace function.

 

[István Hirsch]

István

Are you saying that it didn't work as I posted?
I thought that having specified the final 3 parameters of the Replace function in the first (inner) Replace, that those parameters would automatically flow/apply to the second (outer) Replace function.

Important: I am in another date system.

I got two different results with the original and the modified UDF.

Excel Workbook
	A	B	C
1		Original UDF	Modified UDF
2	08/15/2014 12:08:29 PM PDT	No date found	41866
3	08/28/2014 07:36:38 PM PDT	No date found	41879
4	08/22/2014 05:56:08 AM PDT	41873	41873
5	08/24/2014 09:43:06 AM PDT	41875	41875
6	08/22/2014 07:14:09 AM PDT	41873	41873
7	08/22/2014 05:55:31 AM PDT	41873	41873
8	08/16/2014 02:27:11 PM PDT	No date found	41867
9	08/22/2014 05:51:59 PM PDT	No date found	41873
10	08/26/2014 12:04:44 PM PDT	No date found	41877
11	08/19/2014 11:44:35 AM PDT	41870	41870
12	08/22/2014 07:50:39 AM PDT	41873	41873
13	08/27/2014 01:07:43 PM PDT	No date found	41878
Sheet