☰

将时间字符串/文本转换为时间（可计算）

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2016
将时间字符串/文本转换为时间（可计算）

我有一列数据，由如下字符串组成：

第 1 行：46分06秒。

第 2 行：15天 5小时 09分 33秒

第 3 行：17时 24分 59秒

ETC

我想将这些字符串转换为数字或时间格式，以便对它们进行计算，但当前格式使各种公式难以使用。有什么想法吗？

答案1

Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
    Select Case Right(Trim(tmp), 1)
        Case "d"
            convert_text_to_interval = convert_text_to_interval + Val(tmp)
        Case "h"
            convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
        Case "m"
            convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
        Case "s"
            convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
    End Select
Next
Exit Function

error_handler:
convert_text_to_interval = 0
End Function

必须将该函数插入到公共模块中（而不是工作表或类模块中！）。

插入后，它将在公式主表中的用户定义函数部分中可用。或者可以直接插入（例如，作为=convert_text_to_interval(A1)）。

用法

间隔部分之间的空格是强制性的（1h30m不允许，将被视为1m，见下文）。

单位字母必须与值相邻（1 h 30 m不允许，将被视为0h 0m，见下文）。

没有正确单位字母的部分将被忽略。如果单位部分包含多于 1 个字母，则使用最后一个字母，其他所有字母都将被忽略 ( 1hm== 1m)。除最后一个单位字母外，所有非数字符号都将被忽略。

允许多个部分具有相同的单位（1h 15m 30m== ）。1h 45m

.允许使用小数部分（小数点分隔符 - 点）（ 1.5h）。允许使用负值（2h -20m== 1h 40m）。

使用此函数的单元格的 NumberFormat 可以是数字也可以是时间（最后一种情况下，推荐的格式是 Cell.NumberFormat = "[h]:mm:ss" - 它不会将一天中的小时数截断，例如，15d 5h 09m 33s将显示为365:09:33，而5:09:33不是“h:mm:ss”格式）。

答案2

解析文本字符串中的“d”、“h”、“m”、“s”值并转换为时间。

样本数据输出

这不会替换字符串值。相反，使用此公式为时间算法创建一个辅助列。

  • 为了能够在时间公式单元格中显示超过 24 小时的时间，请使用[h]:mm:ss时间格式。
  • 时间字符串中的所有值和字母对都是可选的。如果存在字母，则必须附加值。
  • 值与单位字母之间没有空格3h（3 h不是：3 个空格 h）
  • 用空格将一对与下一对分开。 3d 2h（3d 空格 2h）
    这是一个错误：（3d2h如果它有两位数字，比如 中的 11 3d11h，那么应该没问题）。
  • 字母不区分大小写（例如“h”或“H”）。

多行格式的公式：直接粘贴到公式栏中，
以避免将公式拆分到多行上。

=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))

该值I11是带有时间字符串的单元格（搜索和替换）。
- 或创建（插入）辅助列。
- 插入新行。
- 在辅助列的新行中粘贴公式。
- 剪切单元格 I11 并粘贴到新行的时间字符串列中（假设 I11 未被现有公式引用）。
- 将原始 I11 的内容复制回 I11 的公式栏，这样相对地址就不会移回，只有内容才会移回。

答案3

您的问题也可以通过使用一些辅助列来解决：

在此处输入图片描述

怎么运行的：

  • 范围内的样本数据A4:E29。
  • D, H, M & S表示Day, Hour, Minute & Second。

  • 公式B4& 填写：

    =VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24

  • 公式C4& 填写：

    =IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1

  • 公式D4& 填写：

    =IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1

  • 公式E4& 填写：

    =IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1

  • 最终公式F4：

=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")

注意：

  • 根据需要调整公式中的单元格引用。
  • 范围的单元格格式A4:E29为 GENERAL。
  • 范围的单元格格式F4:F29为 [h]:mm:ss。
  • 一旦完成工作，您就可以隐藏辅助列。

相关内容