☰

Excel 公式根据文本字符串（如上午 9:00 — 下午 12:30）计算时间持续时间

tag-icon • tag-icon microsoft-excel database worksheet-function spreadsheet
Excel 公式根据文本字符串（如上午 9:00 — 下午 12:30）计算时间持续时间

我正在尝试开发一个 Excel 公式，用于计算以下格式的时间范围内的字符串的实际时间持续时间：1:00am – 3:00am

根据本文看起来应该像减去两个时间一样简单。例如，要获取两个时间（4:55:00）之间的小时、分钟和秒数，文章说要执行以下操作：

=TEXT(B2-A2,"h:mm:ss")

以下是我取得的进展：

=TEXT(RIGHT(B2,SEARCH(" – ",B2))-LEFT(B2,SEARCH(" – ",B2)),"h:mm:ss")

我想知道问题是否可能是我的文本字符串除了时间之外没有“日期”部分。唯一的解决方案是能够执行减法运算以将日期“伪造”到字符串中吗？或者除了简单地分别分解小时和分钟部分并以此方式计算持续时间之外，还有其他替代方法吗？如果可能的话，我还想补偿任何潜在的下午到上午的时间段，例如晚上 9:00 到 12:00。

答案1

这是其中一个复杂的解决方案。最好只是将其粘贴进去并希望得到最好的结果，而不是坐下来分析它。

=INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)) & ":" & TEXT((MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)-INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)))*60,"00")

编辑：最后一次修复了公式。

处理评论：

要添加这些输出，要添加这些输出的范围，您可以使用以下公式：

=TEXT(SUM(VALUE(B2:B3)),"h:mm:ss")

您必须将其作为数组公式输入。输入公式时按 Ctrl+Shift+Enter 即可。

答案2

如果你可以在“am”或“pm”前加一个空格那么这样做就可以了：

=TEXT(RIGHT(B2,LEN(B2)-SEARCH(" - ",B2)-2) -
 LEFT(B2,SEARCH(" - ",B2)-1),"h:mm:ss")

这是一个基本的，可以处理 am/pm 之前没有空格的情况，但它并不强大或完整，因为它无法处理很多极端情况，只是想把它拿出来展示一些不同的处理方式。我们需要知道你将解析的所有可能的字符串类型，才能得出一个真正完整的解决方案：

=TEXT((MID(B2,SEARCH(" - ",B2)+3, LEN(B2)-SEARCH(" - ",B2)-4) &  
       IF(ISERROR(SEARCH("am",RIGHT(B2,8))), " pm", " am")) - 
      (LEFT(B2,SEARCH(" - ", B2)-3) & 
       IF(ISERROR(SEARCH("am",LEFT(B2,8)))," pm"," am")),
      "h:mm:ss")

答案3

正如 Lance Roberts 所说，“am” 或 “pm” 之前需要有空格。

您可以使用以下公式：

=SUBSTITUTE(SUBSTITUTE(B2; "am"; " am"); "pm"; " pm")

然后，尝试一下这个：

=TEXT(TIMEVALUE(RIGHT(B2;SEARCH(" - ";B2;1)))-TIMEVALUE(LEFT(B2;SEARCH(" - ";B2;1)));"h:mm:ss")

答案4

最大的问题是时间需要在分钟和上午/下午之间留一个空格：1:00 am - 3:00 am或9:00 pm - 12:00 am。

这样，您可以使用以下公式：

=TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))+
IF(TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))<0,1,0)

并将单元格格式化为自定义格式h:mm:ss。

相关内容