Google Sheets非标准时间格式化、时长计算与可视化方案

我在这份Google Sheets电子表格中存储了按日录入、附带AM/PM标识的开始与结束时间数据，需要整理计算两个时间点的间隔小时数，最终生成与下方目标效果示例一致的统计图表。

初始数据 #

目标效果 #

• 原始数据为自由文本输入，并非标准时间格式：字符长度通常为1、2、4、5位，整体范围在1-5位之间，根据是否输入冒号存在多种格式变体，例如1、10、130、1:30、1130、12:30。

现有转换逻辑公式如下：

IF(RIGHT(B2,2)="AM",text(IF(AND(LEN(C2)>0,LEN(C2)<3), 
C2&":00",IF(LEN(C2)>3, C2, "")), "HH:MM AM/PM"),
IF(RIGHT(B2,2)="PM",text(IF(AND(LEN(C2)>0,LEN(C2)<3), 12+C2&":00",IF(LEN(C2)>3,
12+split(C2,":")&":"&RIGHT(C2,2), "")), "HH:MM AM/PM"),""))

该方案通过判断文本长度识别已符合时间格式的内容，对不符合格式的内容做转换，对PM时段的时间值统一加12处理，空单元格返回空结果。

• 该公式无法作为数组公式批量运行，暂未定位故障原因；
• 方案无法适配不带冒号的3位、4位时间文本，例如1120、145这类条目无法正确解析，会出现7:00 - 1120计算结果为7:00的错误。

• 考虑使用REGEX正则表达式实现任意格式文本到标准时间格式的转换，也可通过判断从右侧数第3位是否为:的逻辑完成格式识别；
• 数据重组层面预计需要组合使用filter、transpose、query等函数，落地前提是先实现可支持数组批量运行的时间格式转换逻辑。

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