VBA时间差转HH/MM/SS及分钟取整规则改造技术咨询

First, let's address your modified code snippet: it has a couple of critical issues that will cause unexpected behavior:

• You're comparing a numeric cell value to the string literal "30" instead of the number 30. This can lead to type mismatch errors or incorrect comparisons.
• After adjusting the hours and minutes, your existing seconds calculation will produce wrong values because it relies on the modified hour/minute values instead of accounting for the rounding rule.
• Setting the minute cell to "00" (a string) instead of the number 0 can cause formatting or calculation issues later.
• Your rounding rule implies seconds should be set to 0 once we adjust minutes/hours, which isn't handled in your snippet.

Here's the corrected approach & code #

Instead of modifying cell values directly mid-calculation, let's compute the raw time difference first, apply the rounding rule to minutes/hours, then write the final values to the sheet. This makes the code cleaner and less error-prone.

Replace your existing If nextCell.Value <> curCell.Value Then ... End If block with this:

If nextCell.Value <> curCell.Value Then
    Dim totalSeconds As Long
    eDate = ws1.Cells(tymval, 1).Offset(0, 1)
    totalSeconds = DateDiff("s", StartDate, eDate)
    
    ' Calculate raw hours, minutes, seconds
    mHours = Int(totalSeconds / 3600)
    mMinutes = Int((totalSeconds Mod 3600) / 60)
    
    ' Apply rounding rule to minutes
    If mMinutes <= 30 Then
        mMinutes = 30
        mSeconds = 0 ' Set seconds to 0 per rounding rule
    Else
        mMinutes = 0
        mHours = mHours + 1 ' Round up to next hour
        mSeconds = 0 ' Set seconds to 0 per rounding rule
    End If
    
    ' Write final values to worksheet
    ws1.Cells(tymval, 3).Value = mHours
    ws1.Cells(tymval, 4).Value = mMinutes
    ws1.Cells(tymval, 5).Value = mSeconds
    ws1.Cells(tymval, 6).Value = totalSeconds ' Optional: keep total seconds for reference
    
    StartDate = ws1.Cells(tymval, 1).Offset(1, 1)
End If

Key improvements: #

• We calculate the total seconds upfront, which simplifies all subsequent calculations.
• Use numeric comparisons (mMinutes <= 30 instead of comparing to a string) to avoid type issues.
• Directly work with variables (mHours, mMinutes, mSeconds) instead of modifying cell values repeatedly, making the code easier to debug.
• Explicitly set seconds to 0 since your rounding rule focuses on minutes/hours (no need to keep partial seconds).
• Clean up redundant Int() calls since we're already calculating integer values via division/modulo operations.

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