Cannot change the format after concatenating dates in Excel VBA

user_7588080 0
Viewed 106

I have dates formatted as text. I want to change these dates' format from "dd.mm.yyyy hh:mm" to "yyyy.mm.dd". (eg. "04.05.2020 10:33" to "2020.05.04") I use the following code (the original dates are in column "K"):

    Dim cell As Range
For Each cell In Range(Range("K2"), Range("K2").End(xlDown))
    cell.Offset(0, 7).Value = Mid(cell.Value, 7, 4) & "." & Mid(cell.Value, 4, 3) & Left(cell.Value, 2)
Next

The newly created dates cannot be formatted, though and so when I try to use a vlookup function on them, the function fails, saying it couldn't find the value in the lookup table. (dates are ok in the lookup table)

I need to manually enter every cell and hit enter and only after that will excel start recognizing the format. I also found that if I use the manual "replace" function of excel like this: https://i.stack.imgur.com/U3k5e.png, and replace the dots with dots, excel will once again start to recognize the format, however it won't recognize any format when I use the following code:

Range(Range("R2"), Range("R2").End(xlDown)).Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Can someone help? How can I convert the format of these concatenated values in vba so that vlookup will recognize them?

Thanks!

date-formatting concatenation vba excel date
3 Answers

Try this:

cell.Offset(0, 7).Value = Format(Mid(cell.Value, 7, 4) & " " & Mid(cell.Value, 4, 3) & " " & Left(cell.Value, 2), "yyyy.mm.dd")

The yyyy.mm.dd at the end tells it how you want it formatted. You also need to use a space or a slash (/) between the 3 functions because it doesn't recognize a period as a separator for it.

For example formatting the month. Take January:

m = 1

mm = 01

mmm = Jan

mmmm = January

Edit:

The only way I could see really doing it then is:

Dim cell as range

For Each Cell in Range(Range("K2"), Range("K2").End(xlDown))
    Cell.Value = Format(Replace(Cell.Value,".","/"), "yyyy.mm.dd")
Next
user_26321650

Convert Date & Time As String to Date

Option Explicit

Sub createDate()

    Dim Data As Variant
    Dim currString As String
    Dim currDate As Date
    Dim i As Long
    With Range(Range("K2"), Range("K2").End(xlDown))
        Data = .Value
        For i = 1 To UBound(Data, 1)
            currString = Data(i, 1)
            currDate = DateSerial(CLng(Mid(currString, 7, 4)), _
                CLng(Mid(currString, 4, 2)), CLng(Left(currString, 2)))
            Data(i, 1) = currDate
        Next i
        With .Offset(, 7)
            .Value = Data
            .NumberFormat = "yyyy.mm.dd"
            '.Columns.AutoFit
        End With
    End With

End Sub
user_98140690

I ended up using the following code:

   For Each cell In Range(Range("K2"), Range("K2").End(xlDown))
    cell.Offset(0, 7).Value = Mid(cell.Value, 7, 4) & "/" & Mid(cell.Value, 4, 2) & "/" & Left(cell.Value, 2)
Next

Turns out I just had to use slashes to separate the concatenated parts, this allowed me to freely change the formatting of the newly created dates.

Special thanks to Simon, his answers helped a lot!

user_75880800
Related