☰

如何将“1 天，14 小时，16 分钟”之类的文本转换为时间格式？

tag-icon • tag-icon microsoft-excel worksheet-function
如何将“1 天，14 小时，16 分钟”之类的文本转换为时间格式？

我需要将显示某事物有多老的文本转换为时间格式，以便 COUNTIFS 可以过滤特定时期内的数据。

以下是一些数据示例：

1 day(s), 14 hour(s), 16 minute(s)
35 day(s), 6 hour(s), 17 minute(s)
14 hour(s), 7 minute(s)

我曾考虑过使用具有 LEFT、MID、RIGHT 属性的 TIME，但是因为它包含超过 1 天的天数，并且并不总是显示 2 位数字，所以我不知道如何实现这一点。

答案1

尝试

=IF(ISERROR(FIND("day",A1)),0,LEFT(A1,FIND("day",A1)-1))+0+TIME(IF(ISERROR(FIND("hour",A1)),0,TRIM(MID(" "&A1,FIND("hour"," "&A1)-3,2)))+0+0,IF(ISERROR(FIND("minute",A1)),0,TRIM(MID(A1,FIND("minute",A1)-3,2)))+0,0)

结果将是一个十进制数，小数点前的数字表示天数，小数部分表示时间。按您的喜好设置格式。

编辑更正公式以允许 A1 中的值以个位数小时开头。

在此处输入图片描述

答案2

这是解决这个问题的另一种方法，供您参考。

时间段以天、小时和分钟表示。一个或多个时间元素（天、小时或分钟）可能缺失。时间元素以逗号分隔。

您可以使用“文本分列”将时间元素拆分为单独的单元格，这样每个单元格都包含一个时间元素，以金额开头，以单位标识符结尾。您可以剥离金额，根据单位将其转换为天数的分数，然后将各部分相加。然后将结果格式化为小时和分钟。

文本分列从当前单元格开始，替换现有值，因此如果要保留现有值，请将数据复制并粘贴到临时区域，然后让 Excel 在那里解析。这是 Excel 将其拆分为列后的样子：

在此处输入图片描述

我添加了一些记录来涵盖存在的时间元素的不同可能性。请注意，第一个元素可以是天、小时或分钟，第二个元素只能是小时或分钟，第三个元素只能是分钟。D 列是格式化为小时和分钟的结果。

为了解释计算，我将添加一些列来显示每个部分发生的情况：

在此处输入图片描述

F、G 和 H 列分别是 A、B 和 C 列中元素的时间转换。J 列是 Excel 时间的总和。

F1中的公式：

=LEFT(A1,FIND(" ",A1)-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"day","hour","minute"},A1))*{1,24,1440})

通过查找第一个空白处来剥离数字。它会搜索单元格以确定其中包含哪个时间单位，然后根据该单位将数字转换为天的分数。

G1中的公式：

=LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"hour","minute"},B1))*{24,1440})

这是类似的，只是数据在数字前包含空格，必须对其进行修剪。

H1中的公式：

=LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)/1440

由于第三个元素只能是分钟，因此我们不需要搜索单位。

现在，每部分都转换为 Excel 时间，可以加起来。但是，可能存在少于三个元素。将它们组合在 J 列中如下所示：

=IFERROR(F1+IFERROR(G1,0)+IFERROR(H1,0),"")

如果原始单元格为空，则返回 null。如果缺少元素，则将结果错误替换为零。D 列中的单个公式只是将上面的单元格引用替换为引用单元格中的公式：

=IFERROR(LEFT(A1,FIND(" ",A1)-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"day","hour","minute"},A1))*{1,24,1440})+IFERROR(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"hour","minute"},B1))*{24,1440}),0)+IFERROR(LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)/1440,0),"")

相关内容