Excel中如何将登录时长按整点时段规整统计（分钟）

前提说明 #

假设原始数据位于A:C列，格式要求：

• A列：登录状态（login/logout），必须成对出现（每一行login紧跟一行logout）
• B列：时间/日期时间（需设置为Excel可识别的时间格式，不能是纯文本）

步骤1：生成时段标签 #

在E列手动输入需要统计的整点时段，例如：

E2: 03:00 - 04:00
E3: 04:00 - 05:00
E4: 05:00 - 06:00
E5: 06:00 - 07:00
...

步骤2：计算各时段登录时长 #

在F2单元格输入以下公式，下拉填充至所有时段行：

=SUMPRODUCT(
    ($A$2:$A$100="login")*
    MAX(0, MIN($B$3:$B$101, TIME(VALUE(LEFT(E2,2))+1,0,0)) - MAX($B$2:$B$100, TIME(VALUE(LEFT(E2,2)),0,0)))*1440
)

公式拆解

• $A$2:$A$100="login"：筛选所有登录行，对应下一行的登出时间在$B$3:$B$101范围
• TIME(VALUE(LEFT(E2,2)),0,0)：从E列的时段标签提取当前时段的起始整点时间
• TIME(VALUE(LEFT(E2,2))+1,0,0)：提取当前时段的结束整点时间
• MIN(登出时间, 时段结束时间) + MAX(登录时间, 时段起始时间)：计算本次登录与当前时段的重叠时间范围
• 差值乘以1440：将小时单位转换为分钟单位
• SUMPRODUCT：汇总所有登录记录在当前时段的时长总和

特殊情况处理 #

1. 跨天登录：如果存在跨天的登录记录（如23:30登录、次日01:15登出），必须将B列设置为日期时间格式（例如2024/05/20 23:30），否则Excel会误判为同一天的时间，导致计算错误。
2. 未配对的登录记录：如果存在单独的login或logout行，公式会自动忽略未配对的记录，不会影响结果。

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