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.sstatic.net/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!
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