google-sheets - 从非常规的日期和时间字符串中获取平均值

Question

我有一个 Alexa 例程，其中我说晚安的日期/时间在 Google 表格中被跟踪 - 所以我可以看到我真正上床睡觉的时间。是的，现在一切都可以跟踪和测量！

但是这些条目是非常奇怪和无用的字符串，而不是日期和时间：

样本数据：

January 23, 2021 at 11:29PM
January 25, 2021 at 12:03AM
January 25, 2021 at 11:27PM
January 26, 2021 at 11:17PM

Alexa 只是将这些非常规的日期/时间字符串转储到 A1-A 中？？？在第一个选项卡上。

我的目标 - 显示我每个月的平均就寝时间。通常我可以破解我的答案，但我无法将这些转换为月/年和时间，更不用说找到一种平均它们的方法了。

转向那些拥有比我更疯狂的技能的人！

一如既往的赞赏。

标签： google-sheets

Answer 1

您可以试试这个（在 C1 中，因为您的数据从 A2:A 开始）：

更新：

= QUERY(ARRAYFORMULA(
            IF(LEN(A2:A), {
                MONTH(REGEXEXTRACT(A2:A, "\D+") & 1),
                REGEXEXTRACT(A2:A, "\D+"),
                IF(TIMEVALUE(REGEXEXTRACT(A2:A, "\d+:\d+.*")) > 0.5,
                    TIMEVALUE(REGEXEXTRACT(A2:A, "\d+:\d+.*")),
                    TIMEVALUE(REGEXEXTRACT(A2:A, "\d+:\d+.*")) + 1)
            }, "")),
        "Select Col1,Col2 ,avg(Col3) where Col1 is not null 
        group by Col1, Col2 Order By Col1 asc label Col1 '#', Col2 'Month', avg(Col3)
        'Average bedtime'
        ")

电子表格演示：这里