如何将Google Sheets中相对时间字符串转换为可排序的估算日期

1. 预设估算规则 #

为了保证排序逻辑正确，统一按如下规则换算相对时间：

• 描述中的a/an统一按数值1计算
• 1天 = 1天
• 1周 = 7天
• 1月 = 30天（估算值，不影响排序顺序）
• 1年 = 365天

2. 写入转换公式 #

假设存储相对时间的列为A列，第一条数据从A2单元格开始，在空白列（例如B列）的B2单元格输入如下公式，之后下拉填充整列即可：

=TODAY() - SWITCH(
REGEXEXTRACT(LOWER(A2), "(day|week|month|year)"),
"day", IF(REGEXMATCH(A2, "^a "), 1, VALUE(REGEXEXTRACT(A2, "\d+"))),
"week", IF(REGEXMATCH(A2, "^a "), 7, VALUE(REGEXEXTRACT(A2, "\d+"))*7),
"month", IF(REGEXMATCH(A2, "^a "), 30, VALUE(REGEXEXTRACT(A2, "\d+"))*30),
"year", IF(REGEXMATCH(A2, "^a "), 365, VALUE(REGEXEXTRACT(A2, "\d+"))*365)
)

公式逻辑说明：

• 先将文本统一转小写，避免大小写匹配失败
• 用正则提取时间单位和数值
• 按预设规则将相对时间转换为距今的总天数
• 用当前日期减去总天数得到估算的评论发布日期

3. 排序操作 #

选中整个数据表区域，按转换后的日期列（即B列）进行升序/降序排序，即可得到正确的时间先后顺序。

可选优化 #

如果需要固定转换后的日期避免每次打开自动更新，可以选中所有转换结果，右键选择「仅粘贴值」，日期就会固化不会再变动。

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