如何在Google Sheets中按月份拆分数据并计算跨月日期时长？

可以通过公式自动实现跨月日期记录的拆分及时长计算，以下是基于Excel/Google Sheets的具体实现步骤：

核心逻辑 #

当start datetime和end datetime分属不同月份时，拆分为两条记录：

1. 第一条记录：从原开始时间到当月最后一天23:59:00，计算该时段时长
2. 第二条记录：从下月第一天00:00:00到原结束时间，计算该时段时长

具体公式（Excel/Google Sheets通用） #

假设原始数据存放在：

• A列：start datetime（如A2为1/15/2024 18:00:00）
• B列：end datetime（如B2为2/12/2024 14:00:00）

1. 判断是否需要拆分 #

在C2单元格输入公式，标记跨月记录：

=IF(MONTH(A2)<>MONTH(B2),"需拆分","无需拆分")

2. 生成第一条记录的结束时间及时长 #

• D2（第一条记录结束时间）：

=IF(C2="需拆分",EOMONTH(A2,0)+TIME(23,59,0),B2)

• E2（第一条记录时长，单元格格式可设为[h]:mm或数值型小时）：

=D2-A2

3. 生成第二条记录的开始时间、结束时间及时长 #

• F2（第二条记录开始时间）：

=IF(C2="需拆分",DATE(YEAR(A2),MONTH(A2)+1,1),"")

• G2（第二条记录结束时间）：

=IF(C2="需拆分",B2,"")

• H2（第二条记录时长）：

=IF(C2="需拆分",G2-F2,"")

示例计算结果 #

针对你提供的示例数据：

start datetime - 1/15/2024 18:00:00
end datetime - 2/12/2024 14:00:00

拆分后两条记录的时长：

• 第一条：1/15/2024 18:00:00 至 1/31/2024 23:59:00，时长为389小时59分钟
• 第二条：2/1/2024 00:00:00 至 2/12/2024 14:00:00，时长为302小时

批量处理提示 #

如果需要批量生成拆分后的记录，可以通过筛选C列=需拆分的行，将对应F、G、H列的内容复制到新区域，再与无需拆分的记录合并即可。

内容的提问来源于stack exchange，提问作者Muhammad Syukri Mansor