vba - Excel中的强制日期格式

Question

我有一列包含正确格式的日期:



我正在尝试首先更改“。”到“/”，下面的代码可以正常工作，但它会自动修改日期格式并将日期识别为月份，如果它低于 12。

'2) Replace "." by "/"
    'Range("C:C").NumberFormat = "@" ' I tried with AND without this line...no difference
    'Range("C:C").NumberFormat = "dd.mm.yyyy hh:mm:ss" ' if I add this then only "." starting from 13th of January are reaplced by "/"
    'Range("C:C").NumberFormat = "dd/mm/yyyy hh:mm:ss" ' no differences at all neither
    Columns("C:C").Select
    Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

太烦人了...任何想法如何解决这个问题？

Answer 1

如果您不介意遍历数据，可以执行以下操作:

Sub Test()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("SheetName")

For Each Cell In ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
    Cell.Value = CDate(Replace(Cell.Value, ".", "/"))
Next Cell

End Sub

或者，如果您愿意，可以使用辅助列并使用以下公式:

=TEXT(DATE(MID(C2,SEARCH(".",C2,SEARCH(".",C2,1)+1)+1,4),MID(C2,SEARCH(".",C2,1)+1,2),MID(C2,1,2))+TIMEVALUE(RIGHT(C2,8)),"dd/mm/yyyy hh:mm:ss")