如何编写Excel VBA将日期转换为当日23:59避免重复累加

Got it, let's tackle this problem head-on. The key issue with your previous approach was that you were adding time to the existing cell value—so every time you ran the macro, it kept stacking hours and eventually pushed the date to the next day. Instead, we need to base the 23:59 time on the pure date portion of the cell, ignoring any existing time, so it never accumulates no matter how many times you run the macro.

Solution Code for Single Cell (e.g., C1) #

Here's a reliable macro that will set any date (with or without existing time) to 23:59 of the same day:

Sub SetEndOfDay()
    ' Replace "YourSheetName" with your actual worksheet name
    Dim targetCell As Range
    Set targetCell = ThisWorkbook.Sheets("YourSheetName").Range("C1")
    
    ' Extract just the date part (ignores any existing time in the cell)
    Dim baseDate As Date
    baseDate = DateValue(targetCell.Value)
    
    ' Set the cell to the end of the day (23:59:59)
    targetCell.Value = baseDate + TimeSerial(23, 59, 59)
    
    ' Optional: Format the cell to display date and time clearly
    targetCell.NumberFormat = "dd/mm/yyyy hh:mm"
End Sub

How This Works #

• DateValue(targetCell.Value): This strips away any existing time from the cell and returns only the pure date (e.g., for "15/June 12:00 HRS", it gives you just 15/June).
• TimeSerial(23, 59, 59): Creates a time value for 23 hours, 59 minutes, 59 seconds. Adding this to the pure date gives you the last second of the day (use TimeSerial(23, 59, 0) if you don't need seconds).
• Since we always start from the pure date portion, running the macro multiple times won't shift the date—it will just reset the time to 23:59 every time.

Extend to a Range of Cells #

If you need to fix multiple cells (like an entire column), use this loop version:

Sub SetEndOfDayForRange()
    Dim targetRange As Range
    Dim cell As Range
    
    ' Define your target range (e.g., C1 to C10 on your sheet)
    Set targetRange = ThisWorkbook.Sheets("YourSheetName").Range("C1:C10")
    
    For Each cell In targetRange
        ' Only process cells with valid dates to avoid errors
        If IsDate(cell.Value) Then
            Dim baseDate As Date
            baseDate = DateValue(cell.Value)
            cell.Value = baseDate + TimeSerial(23, 59, 59)
            cell.NumberFormat = "dd/mm/yyyy hh:mm"
        End If
    Next cell
End Sub

Test with Your Scenario #

For your example:

• If C1 is "15/June", the macro sets it to "15/June 23:59"
• If C1 is "15/June 12:00 HRS", it still sets it to "15/June 23:59"
• Your D1 formula =IF(C1>B1,"OUT OF RANGE","Okay !") will now correctly show "Okay !"

内容的提问来源于stack exchange，提问作者Wen Liang