在 Excel 中，我正在寻找一种简单的方法将 24 小时格式的时间范围（各种格式）转换为小时和分钟。
例如，我可能有不同的小时/分钟范围格式：
9-12 // 结果 03:00
9-12:45 // 结果 03:45
09:30-15:40 // 结果 06:10
9:20-13 // 结果 03:40
我可以假设
- 所有时间都在同一天 - 即午夜至晚上 11 点 59 分之间。
- 范围字符串中没有空格
- 小时/分钟范围之间总是有一个连字符
- 分钟数左侧总是会填充 0（例如 9:05，但绝不会是 9:5）
我可以使用以下公式转换正确格式的 24 小时范围（例如 09:30-15:45）：
=TEXT(MOD(TEXT(RIGHT(C2,LEN(C2)-FIND("-",C2)),"hh:mm")-TEXT(LEFT(C2,FIND("-",C2)-1),"hh:mm"),1),"hh:mm")
我可以将单个整数小时（例如 9）转换为 hh:mm 格式：
=TEXT(A1/24,"hh:mm") // 结果 09:00
我可以将未填充的 h:mm 格式（例如 9:30）转换为填充的 hh:mm 格式：
=TEXT(B3,"hh:mm") // 结果 09:30
但是，我没有看到任何可以将所有内容转换为 hh:mm 的函数，因此我可以应用我的公式。我不希望在公式中使用一堆 IF 语句来检查冒号，但如果有必要，我会这样做。
答案1
使用具有函数 (2021 或 365) 的 Excel 版本，LET()以下公式适用于 OP 中指定类型并放置在单元格中的字符串A1。包含公式的结果单元格需要使用 进行格式化hh:mm。
=LET(
s, A1,
d, FIND("-", s),
time2, RIGHT(
s,
LEN(s) - d
),
time1, LEFT(s, d - 1),
(
VALUE(time2) /
IFERROR(
--(
FIND(
":",
time2
) > 0
),
24
)
) -
(
VALUE(time1) /
IFERROR(
--(
FIND(
":",
time1
) > 0
),
24
)
)
)
找到字符串表达式中时间分隔符之前和之后的位-，方法与 OP 中的方法非常相似，使用通常的字符串函数并将其分配为变量时间1和时间2LET()并通过函数转换为数值VALUE()。其中时间字符串不包含冒号，则其数值除以24，否则除以1。
中的除数LET()由下式给出
IFERROR(--(FIND(":",time2)>0),24)对于字符串时间2 和
IFERROR(--(FIND(":",time1)>0),24)对于字符串时间1
所以不，IF这只是一种轻微的作弊行为IFERROR。
该函数的可用性LET()只是允许以简写方式表达 Excel 的重复部分。这意味着包含两个时间的字符串表示为s并定义为单元格A1，其中分隔符的位置位于此字符串中的字符位置d在字符串内。
FIND("-",A1)在查找分隔符前后的字符串部分（“时间字符串”）时，不要重复使用短语，例如
RIGHT(A1, LEN(A1) - FIND("-",A1))和
LEFT(A1, FIND("-",A1)-1)
在 OP 中，它被简单地替换为d在LET版本中。
将时间字符串转换为除以 1 或 24 的数字时间值的表达式涉及在两个函数中使用时间字符串：VALUE和FIND（这次查找“:”）。这种方法允许短语
VALUE(RIGHT(A1, LEN(A1) - FIND("-",A1))) / IFERROR(--(FIND(":",RIGHT(A1, LEN(A1) - FIND("-",A1)))>0),24)
LET()在函数中缩短为
VALUE(time2) / IFERROR(--(FIND(":",time2)>0),24)
如果不使用LET函数，公式可以写成：
=(VALUE(RIGHT(A1, LEN(A1) - FIND("-",A1)))/IFERROR(--(FIND(":",RIGHT(A1, LEN(A1) - FIND("-",A1)))>0),24))-(VALUE(LEFT(A1,FIND("-",A1)-1))/IFERROR(--(FIND(":",LEFT(A1,FIND("-",A1)-1))>0),24))
答案2
您可以使用该TEXTSPLIT功能。
使用 Microsoft 365，在某个单元格中输入以下公式。所有结果都会显示下来。
=BYROW(
$A$1:$A$4,
LAMBDA(arr,
LET(
s, TEXTSPLIT(arr, "-"),
sB, TEXTSPLIT(INDEX(s, 1), ":"),
sBh, INDEX(sB, 1),
sBm, IFERROR(INDEX(sB, 2), 0),
sE, TEXTSPLIT(INDEX(s, 2), ":"),
sEh, INDEX(sE, 1),
sEm, IFERROR(INDEX(sE, 2), 0),
B, TIME(sBh, sBm, 0),
E, TIME(sEh, sEm, 0),
E - B
)
)
)
$A$1:$A$4是要处理的范围。- 这可以用任何单列范围引用代替
s：用连字符分割字符串sB：将 的第一部分拆分s为冒号sBh：开始时间的小时部分sBm：开始时间的分钟部分IFERROR如果没有会议纪要
sE、sEh和 的过程类似sEmB和E：创建实际时间值
笔记：结果需要格式化，否则[hh:mm]它们将仅显示一天的一小部分
