excel - 领先的零 vba excel 错误日期

Question

我有这个来自数据库的日期，我想在 VBA excel 中修复日期，因为 excel 在过滤列时用月份切换日期

27/08/2018
31/08/2018
12/9/2018
2/8/2018    wrong date reported at filter in excel need 02/08/2018
6/8/2018    wrong date reported at filter in excel need 06/08/2018
13/08/2018
17/08/2018
20/08/2018
20/08/2018

我试过这个

For i = 2 To lastRow
    Dim fDate As Date
    Dim dayF As String
    Dim monthF As String
    Dim yearF As String


    Set r = Cells(i, Column_DateStamp)
    strDate = Split(r.Text, "/")

    dayF = CStr(Format(strDate(0), "00"))
    monthF = CStr(Format(strDate(1), "00"))
    yearF = CStr(Format(strDate(2), "0000"))
    fDate = Format(DateSerial(strDate(2), CStr(Format(strDate(1), "00")), CStr(Format(strDate(0), "00"))), "dd/mm/yyyy")
    r.Clear
    r.Value = fDate
Next i

标签： excelvba

Answer 1

日期格式与您的本地日期格式不匹配，因此 Excel 正在尝试转换。

您需要输入日期并适当地格式化或制作单元格文本，以便 Excel 不会尝试转换。

Dim i As Long
For i = 2 To lastRow
    Dim fDate As Date

    Dim r As Range

    Set r = Cells(i, Column_DateStamp)
    strDate = Split(r.Text, "/")

    fDate = DateSerial(strDate(2), strDate(1), strDate(0))

    r.Clear
    'True date - comment out if you want string
    r.NumberFormat = "dd/mm/yyyy"
    r.Value2 = fDate
    'String - Uncomment if you want string
'    r.NumberFormat = "@"
'    r.Value2 = Format(fDate, "dd/mm/yyyy")

Next i