Google Sheets计算每日日志有效时长：处理乱序时间戳与长间隔

需求 #

• 处理库存货物扫描进出的时间戳日志，按日统计有效工作时长
• 统计规则：计算当日第一条到最后一条日志的时长，但排除相邻日志间隔超过1小时的时间段
• 需解决日志乱序问题：因编辑操作导致时间戳顺序与行位置不一致

日志数据 #

注：以下日志中存在两类异常：部分时间戳乱序、部分相邻记录间隔超1小时

Entry Time Stamp
8/29/2022 8:52:17
8/29/2022 9:13:59
8/29/2022 9:14:03
8/29/2022 9:52:16
8/29/2022 10:45:36
8/29/2022 10:46:05
8/29/2022 11:27:00
8/29/2022 11:36:53
8/29/2022 11:30:05
8/29/2022 12:14:44
8/29/2022 12:16:07
8/29/2022 12:53:42
8/29/2022 12:54:51
8/29/2022 13:00:32
8/29/2022 13:19:10
8/30/2022 10:45:51
8/30/2022 10:54:41
8/30/2022 11:23:07
8/30/2022 12:16:05
8/30/2022 12:43:44
8/30/2022 12:54:22
8/30/2022 12:55:27
8/30/2022 14:01:29
8/30/2022 14:39:18
8/30/2022 15:08:43
8/31/2022 9:16:35
8/31/2022 9:43:28
8/31/2022 9:46:05
8/31/2022 9:48:01
8/31/2022 10:40:08
8/31/2022 11:08:59
8/31/2022 11:14:31
9/1/2022 10:51:19
9/1/2022 11:16:09
9/1/2022 11:17:07
9/1/2022 11:59:28
9/1/2022 12:01:48
9/1/2022 12:05:31
9/1/2022 12:07:20
9/1/2022 12:08:59
9/1/2022 12:10:30
9/1/2022 12:11:42
9/1/2022 12:22:05

异常明细：

• 8/29/2022：11:27:00、11:36:53、11:30:05 时间戳乱序
• 8/30/2022：12:55:27 与 14:01:29 间隔超1小时

电子表格实现步骤（Excel/Google Sheets通用） #

假设时间戳数据在A列（A1为表头，A2开始为数据）：

1. 修复日志乱序 #

• 插入辅助列（B列），提取日期：=INT(A2)
• 选中A列+B列，按「B列升序→A列升序」排序，确保同日期内时间戳从早到晚排列

2. 计算有效时间间隔 #

• 插入C列，计算同日期内相邻记录的时间差：=IF(INT(A2)=INT(A1), A2-A1, 0)
• 插入D列，筛选出间隔≤1小时的有效时长：=IF(C2<=TIME(1,0,0), C2, 0)

3. 按日汇总有效时长 #

• 插入E列，提取标准格式日期：=TEXT(A2,"yyyy/mm/dd")
• 用SUMIFS函数汇总每日时长，例如计算8/29的有效时长：=SUMIFS(D:D, E:D, "2022/08/29")
• 或使用数据透视表：将E列拖入行区域，D列拖入值区域，汇总方式选「求和」，快速得到每日总有效时长

示例日志计算结果 #

• 2022/08/29：4小时26分53秒（无超1小时间隔，乱序修复后直接取首尾时长）
• 2022/08/30：3小时16分50秒（排除12:55到14:01的无效间隔）
• 2022/08/31：1小时57分56秒
• 2022/09/01：1小时30分46秒

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