事件昼夜发生时长计算：求公式/VBA/Python实现方案

一、Excel公式实现（假设昼间为7:00-19:00，可自行调整） #

以下公式适配当日内、跨单日、跨多日等场景，需将事件开始时间设为单元格A2，结束时间设为B2：

昼间时长计算： #

=IF(A2=B2,0,
 SUMPRODUCT(
  TEXT(
   MAX(MIN(B2,DATE(YEAR(A2)+ROW(INDIRECT("1:"&DATEDIF(A2,B2,"d")+1)),MONTH(A2),DAY(A2))+TIME(19,0,0),A2),
   "yyyy-mm-dd hh:mm:ss")
  -TEXT(
   MIN(MAX(A2,DATE(YEAR(A2)+ROW(INDIRECT("1:"&DATEDIF(A2,B2,"d")+1)),MONTH(A2),DAY(A2))+TIME(7,0,0)),B2),
   "yyyy-mm-dd hh:mm:ss")
 )
)

夜间时长计算： #

=B2-A2-昼间时长单元格

说明：公式通过逐天计算当日昼间时段与事件区间的重叠时长，累加得到总昼间时长；夜间时长直接用事件总时长减去昼间时长即可。

二、VBA自定义函数实现 #

打开Excel按Alt+F11进入VBA编辑器，插入模块后粘贴以下代码：

Function CalculateDayNightDuration(startTime As Date, endTime As Date, dayStart As Double, dayEnd As Double) As Variant
    Dim totalDay As Double, totalNight As Double
    Dim currentDay As Date, nextDay As Date
    Dim dayStartTime As Date, dayEndTime As Date
    
    totalDay = 0
    totalNight = 0
    
    If startTime >= endTime Then
        CalculateDayNightDuration = Array(0, 0)
        Exit Function
    End If
    
    currentDay = DateValue(startTime)
    nextDay = currentDay + 1
    
    Do While currentDay <= DateValue(endTime)
        dayStartTime = currentDay + dayStart
        dayEndTime = currentDay + dayEnd
        
        Dim overlapStart As Date, overlapEnd As Date
        overlapStart = IIf(startTime > dayStartTime, startTime, dayStartTime)
        overlapEnd = IIf(endTime < dayEndTime, endTime, dayEndTime)
        
        If overlapStart < overlapEnd Then
            totalDay = totalDay + (overlapEnd - overlapStart)
        End If
        
        currentDay = nextDay
        nextDay = currentDay + 1
    Loop
    
    totalNight = (endTime - startTime) - totalDay
    CalculateDayNightDuration = Array(totalDay * 24, totalNight * 24)
End Function

使用方法：在单元格输入=CalculateDayNightDuration(A2,B2,TIME(7,0,0),TIME(19,0,0))，按Ctrl+Shift+Enter（数组公式），返回的第一个值为昼间小时数，第二个为夜间小时数。

三、Python实现方案 #

使用datetime模块处理时间区间，逐天计算重叠时长：

from datetime import datetime, timedelta

def calculate_day_night(start_str, end_str, day_start_hour=7, day_end_hour=19):
    start = datetime.strptime(start_str, "%Y-%m-%d %H:%M:%S")
    end = datetime.strptime(end_str, "%Y-%m-%d %H:%M:%S")
    
    total_day = timedelta(0)
    total_night = timedelta(0)
    
    current_day = datetime(start.year, start.month, start.day)
    next_day = current_day + timedelta(days=1)
    
    while current_day <= end:
        day_start = current_day + timedelta(hours=day_start_hour)
        day_end = current_day + timedelta(hours=day_end_hour)
        
        overlap_start = max(start, day_start)
        overlap_end = min(end, day_end)
        
        if overlap_start < overlap_end:
            total_day += overlap_end - overlap_start
        
        current_day = next_day
        next_day = current_day + timedelta(days=1)
    
    total_night = end - start - total_day
    
    return {
        "昼间时长(小时)": total_day.total_seconds() / 3600,
        "夜间时长(小时)": total_night.total_seconds() / 3600
    }

# 示例调用
result = calculate_day_night("2024-05-20 18:00:00", "2024-05-22 08:00:00")
print(result)

说明：代码支持自定义昼间起始/结束小时，输入时间需为YYYY-MM-DD HH:MM:SS格式，返回昼间和夜间的小时数。

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