Date formats in VBA from Text String

Question

I have an issue with date formats that I cannot get my head around.

I have 2 dates a reporting date which the user inputs Format is MM/DD/YYYY Showing as (11/15/2017) on the speadsheet. This is input from a Userform asking just for the Month and Date but uses the current year.

I then have a series of codes which validates the Data Line matches the ReportDate and produces information. To get Data date I need to look in a text string find a the word PPP and get the numbers proceeding the @ symbol.

I get the day and month value separately as only the day/month is visible in this string and appears like this PPP@11/15

Below is an extract of my code but it is not the full script.

Dim Year1 As Variant
Dim Month1 As Variant
Dim Day1 As Variant

If Not IsError(Mid(PODRng, InStr(1, PODRng, "PPP", vbTextCompare) + 4, 5)) Then                                                                                                        
Year1 = Format(Now(), "YYYY")
Month1 = Mid(PPPRng, InStr(1, PPPRng, "PPP", vbTextCompare) + 4, 2)
Day1 = Mid(PPPRng, InStr(1, PPPRng, "PPP", vbTextCompare) + 7, 2)
PPPTim = Day1 & "/" & Month1 & "/" & Year1
 'PPPTim.NumberFormat = "MM/DD/YYYY"

    If PPPTim = ReportDate Then


        Cell.Offset(0, 49).Value = PPPTim ' 
    ElseIf InStr(1, PODRng, "PPP", vbTextCompare) > 0 Then
        Cell.Offset(0, 49).Value = "Other Date"
    End If

End If

The data source is roughly 3k-5k lines and I have gone through the code and breaking when it gets to this code. I can confirm when checking the Locals both PPPTim and ReportDate are showing as 15/11/2017 but the match is coming through as False.

Hopefully someone can help?

ashleedawg ashleedawg · Accepted Answer · 2017-11-15T12:13:54

Is this what you're looking for?

Dim podRNG As String, n1 As Integer, n2 As Integer, yourDate As Date

podRNG = "PPP@11/15"

n1 = InStr(podRNG, "@") + 1
n2 = InStr(podRNG, "/") + 1

PPPTim = CDate(Mid(podRNG, n1, n2 - n1 - 1) & "/" & Mid(podRNG, n2) & _
    "/" & Format(Now(), "YYYY"))

Returns (on my system):

2017-11-15

...since the data type is Date, the date format will vary based on your Regional Settings.

Date formats in VBA from Text String

1 Answers