VBA for text to date

0
votes

How to apply for all items below E4 and F4,G4,H4 and I4:
The format as seen was has a mixture of "21-07-21" and "8/2/2022" format. Due to text file to excel split using "|" the end result is the mix format of date and text:

=DATE(RIGHT(E2,2)+100,MID(E2,4,2),LEFT(E2,2))
3
excelvbadate
Please provide some sample data using excelguru.ca/… - also reading minimal reproducible example could help to improve your question. - Ike
This usually occurs because you have Open'd a txt or csv file where the date format does not match your windows regional settings. Both forms of dates will be wrong. Better solution is to properly Import the file so Excel can know the incoming date format and translate it properly. Or you can just specify the date format when you run the text-to-columns wizard. - Ron Rosenfeld

3 Answers

0
votes

Please, use the next function:

Function convStrToDate(strD As String) As Date
    If InStr(strD, "-") > 0 Then strD = Replace(strD, "-", "/")
    Dim arrD: arrD = Split(strD, "/")
    If UBound(arrD) = 2 Then
        convStrToDate = DateSerial(CLng(arrD(2)), CLng(arrD(1)), CLng(arrD(0)))
    Else
        MsgBox "String different than necessary pattern..."
    End If
End Function

It can be tested in the next way:

Sub TestConvStrToD()
   Debug.Print convStrToDate("21-07-21")
   Debug.Print convStrToDate("8/2/2022")
End Sub

The above solution assumes that "8/2/2022" means eight of February...

0
votes

If I understand correctly, you need to distinguish two formats:

21-07-21
8/2/2022

First, let's be really sure that the second one means "The eigth of February" and not "The second of august".
In order to distinguish, you can simply use an InStr function to check if the date contains a slash or a hyphen. In case of a hyphen, it's the first format. In case of a slash, it's the second.

Once you have determined the format, the rest is quite easy (pseudo-code):

  1. Locate the separator and take all the digits before that location, that's the day.
  2. Remove the day and the separator.
  3. Locate the separator and take all the digits before that location, that's the month.
  4. Remove the month and the separator.
  5. The rest is the year.

You follow this procedure and this is what you get for your examples:

Example "21-07-21":

temp="21-07-21"
  1. Location=3, day=21
  2. temp="07-21"
  3. Location=3, month=07
  4. temp="21"
  5. year=21 (convert this into 2021)

Example "8/2/2022"

temp="8/2/2022"
  1. Location=2, day=8
  2. temp="2/2022"
  3. Location=2, month=2
  4. temp="2022"
  5. year=2022

I advise you to write a UDF (User-Defined Function) for this: putting all this in a formula might make your formula too complex and unreadable.

Good luck

0
votes

Due to text file to excel split using "|" the end result is the mix format of date and text

It's worth pointing out here, that Text-to-columns allows you to select the incoming date format and outputted data type etc. in the options, which would negate the need to convert these dates if done properly.

However, to answer your question - I think you're after something like this:

Sub test()
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row
        For Each c In .Range("E2:I" & lastrow)
            c.Value = DateSerial(Val(Right(c.Value, 2)), Val(Mid(c.Value, 4, 2)), Val(Left(c.Value, 2)))
        Next
    End With
End Sub