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))

CodePudding user response：

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

CodePudding user response：

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...

CodePudding user response：

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