excel - 将日期转换为一天结束，excel vba

Question

下面的代码不断添加 23:59HRS 到日期，我需要每天运行代码 4-5 次，它会添加直到它更改初始日期。我只想让它直到那个日期的一天结束而不添加它。

Dim x As Integer
Dim test1 As Date
Dim schdWS, compWS As Worksheet
Const H23M59 As Double = 1 - 60 / 86400

Set schdWS = Sheet1
Set compWS = Sheet11

     lastrow = schdWS.Cells(Rows.Count, 8).End(xlUp).Row

        For x = 20 To lastrow

                If IsDate(schdWS.Cells(x, 3).Value) Then
                       Cells(x, 3).Value = CDate(Int(Cells(x, 3).Value + H23M59))
                End If
Next x

End sub

标签： excelvba

Answer 1

用以下内容替换If循环内部

If IsDate(schdWS.Cells(x, 3).Value) Then
    'Get rid of hours, min and secs
    TheDay = Int(schdWS.Cells(x, 3).Value)
    TheEndOfDay = TheDay + H23M59
    'I write the result in col4 for clarity. Replace according to your needs.
    schdWS.Cells(x, 4).Value = Format(TheEndOfDay, "YYYY/MM/DD HH:MM:SS")
End If