转换Google Sheets非标准Alexa日期时间字符串计算月度平均就寝时间

Alexa导入的时间字符串结构高度统一，仅需通过内置函数做格式适配，即可转为Google Sheet可直接计算的标准日期时间值，不需要手动拆分字段。如果你的A1单元格已经是导入的时间字符串（无预留表头），直接把所有辅助列公式从第1行开始输入即可：

1. 在B列（可自行设置表头「标准就寝时间」），B1单元格输入公式，整列向下填充：

   =IF(A1="","", VALUE(SUBSTITUTE(A1, " at ", " ")))
   

   公式逻辑：用SUBSTITUTE把字符串里的" at "替换为空格，再通过VALUE自动识别替换后的内容为标准日期时间值（Google Sheet底层以带小数的序列值存储日期时间，整数部分对应日期，小数部分对应当日时刻，可直接参与算术计算），转换完成后可按需把B列设置为熟悉的日期时间显示格式。
2. 跨零点时间适配优化（解决平均结果偏差的关键）：
   在C列（可自行设置表头「纯就寝时刻」），C1单元格输入公式整列向下填充：

   =IF(B1="","",
     LET(t, MOD(B1,1),
       IF(t < 0.3, t + 1, t)
     )
   )
   

   公式逻辑：先用MOD去掉B列值里的日期整数部分，仅保留时刻的小数值；对小于0.3（即早于7:12的跨零点就寝记录，本质是前一天晚间过了午夜的时间），给时刻值加1，把这类凌晨的时间挪到和晚间就寝时间一致的数值区间，避免后续平均时出现“平均就寝时间在上午”的离谱结果。这个0.3阈值可以根据自身作息调整，比如习惯凌晨2点前入睡，把阈值改成0.2就可以，数值越小，对“过晚跨零点就寝”的判定越宽松。
3. 月度分组标记：
   在D列（可自行设置表头「所属统计月」），D1单元格输入公式整列向下填充：

   =IF(B1="","", EOMONTH(B1,0))
   

   公式逻辑：把同一自然月的所有记录，统一匹配为当月最后一天的日期，作为分组统计的统一维度。

不需要手动做透视表，在任意空白位置输入以下QUERY汇总公式，即可自动按月份输出平均结果：

=QUERY({D1:D, C1:C},
 "select Col1, MOD(AVG(Col2),1)
  where Col1 is not null
  group by Col1
  label Col1 '统计月份', MOD(AVG(Col2),1) '月度平均就寝时间'")

公式说明：汇总得到的平均时刻值会自动取余回到正确的时间序列区间，把对应单元格的显示格式设置为仅展示时间（hh:mm AM/PM），就能直接看到每个月的平均就寝时间。

小贴士：如果不想留C、D列的辅助计算列，直接把所有公式的逻辑嵌套到QUERY函数里也可以实现一键出结果，但分开用辅助列更方便后续核对单条记录的转换准确性，排查错值也更快。

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