Excel中设备停机时长计算问题：特定时段统计公式失效

Got it, let's break this down step by step. You've got a working formula for one device to calculate downtime only within a specific window (your Min & Max time), including handling date rollovers—but when you apply the same logic to other devices, it breaks. First, let's solidify the core logic, then troubleshoot why it's failing elsewhere.

The key here is to calculate the overlap between the device's downtime interval and your target window. Let's formalize the example you gave to make it concrete:

Device stops communicating at 16:00, resumes at 22:00. Target window is 6:00 AM – 18:00 PM. The valid downtime is 2 hours (16:00 to 18:00), since we only count time inside the window.

Here are all the edge cases you need to handle (including date changes):

• Downtime fully inside the window: Just subtract start time from end time.
• Downtime starts inside, ends outside the window: Calculate from downtime start to window end (like your example).
• Downtime starts outside, ends inside the window: Calculate from window start to downtime end.
• Downtime spans multiple days: Split the calculation by day. For example, if downtime runs 20:00 → next day 8:00 with a 6:00–18:00 window:
  • Day 1: 0 hours (20:00 is outside the window)
  • Day 2: 2 hours (6:00 → 8:00)
• Downtime fully covers the window: Count the full length of the window (e.g., 12 hours for 6:00–18:00).
• Downtime fully outside the window: Count 0 hours.

If the logic works for one device but not others, these are the most likely culprits:

• Inconsistent timestamp formats: Some devices might use 12-hour time with AM/PM, others 24-hour time. Your formula might not handle both formats correctly (e.g., parsing "6:00 PM" as 6:00 instead of 18:00).
• Broken date rollover handling: If other devices have downtime that crosses midnight, your formula might not account for the next day's window (e.g., treating 8:00 next day as 8:00 same day, leading to negative or incorrect calculations).
• Missing/Invalid timestamps: Some devices might have blank or malformed "last communication" timestamps. If your formula doesn't include error handling for these, it'll break.
• Incorrect window settings: Double-check that the Min & Max time is configured the same way for all devices—sometimes accidental overrides happen!

1. Validate timestamp consistency: Check all devices' timestamps to ensure they use the same format (e.g., YYYY-MM-DD HH:MM:SS 24-hour time). Convert any non-standard formats to a unified one first.
2. Test cross-day scenarios: Pick a device with downtime spanning midnight and manually calculate the expected overlap. Compare it to your formula's output to spot where the date handling fails.
3. Add error checking: Update your formula to handle edge cases like:
   • Downtime start time being later than end time (a common data glitch)
   • Null/missing timestamps (return 0 or flag the device for review)
4. Verify window parameters: Confirm that the Min & Max time values are identical across all devices—don't assume the formula is pulling the same window for everyone.

Here's a simplified version of the logic to adapt to your tool (spreadsheet, script, etc.):

function calculateValidDowntime(downStart, downEnd, windowStart, windowEnd):
    // Convert all times to datetime objects (critical for date handling)
    startDT = to_datetime(downStart)
    endDT = to_datetime(downEnd)
    windowStartDT = to_datetime(startDT.date() + " " + windowStart)
    windowEndDT = to_datetime(startDT.date() + " " + windowEnd)
    
    totalValid = 0

    // Handle cross-day downtime
    if endDT.date() > startDT.date():
        // Calculate overlap on first day
        day1OverlapStart = max(startDT, windowStartDT)
        day1OverlapEnd = min(windowEndDT, startDT.replace(hour=23, minute=59, second=59))
        totalValid += max(0, day1OverlapEnd - day1OverlapStart)

        // Calculate overlap on second day (extend for more days if needed)
        nextDayWindowStart = to_datetime(endDT.date() + " " + windowStart)
        day2OverlapStart = max(nextDayWindowStart, endDT.replace(hour=0, minute=0, second=0))
        day2OverlapEnd = min(endDT, to_datetime(endDT.date() + " " + windowEnd))
        totalValid += max(0, day2OverlapEnd - day2OverlapStart)
    else:
        // Single-day downtime: find the overlap interval
        overlapStart = max(startDT, windowStartDT)
        overlapEnd = min(endDT, windowEndDT)
        totalValid = max(0, overlapEnd - overlapStart)

    // Convert seconds to hours (adjust units as needed)
    return totalValid.total_seconds() / 3600

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