excel - 如何使用 excel 公式从文本字符串 "7.00 pm to 8.00 pm"或 "11.00 am to 12.00 pm"计算小时差

Question

我想从给定的文本字符串中计算小时数和分钟数，目前我使用一些 excel 内置函数创建了以下公式，

示例文本字符串:

7.00pm to 8.00pm or 9.00am to 11.45pm or 1.20pm to 3.40pm

创建的公式-

=CONCATENATE(REPLACE(SUM(NUMBERVALUE(REPLACE(RIGHT(A1,LEN(A1)-11),5,2,""))-NUMBERVALUE(REPLACE(A1,5,100,""))),2,3," Hour")," ",CONCATENATE(RIGHT(SUM(NUMBERVALUE(REPLACE(RIGHT(A1,LEN(A1)-11),5,2,""))-NUMBERVALUE(REPLACE(A1,5,100,""))),1)*10, " Minutes"))

上面的公式从给定的字符串中获取数值，然后计算差值，然后将值分开，然后与给定的文本连接

这适用于字符串

7.00pm to 9.40pm

输出:

2 Hour 40 Minutes

但我认为我所采取的方法不是最好的方法，而且当时间是上午到下午意味着“上午 9 点到下午 1 点”时它也行不通，那么有没有其他方法可以计算小时差。
或者我们可以微调这个公式...

如果此查询需要任何进一步的信息，请告诉我

Answer 1

时间属于时间。以下公式撤消了输入期间所犯的所有错误并计算了真实的时间值。

=TIMEVALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ".", ":"), "am", " am"), "pm", " pm"), 8))-TIMEVALUE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ".", ":"), "am", " am"), "pm", " pm"), 8))

给单元格一个自定义的数字格式，

h \hou\r\s \a\n\d m \mi\nut\e\s_)

该格式不会改变您拥有可在 future 计算中使用的原始基础值(value)这一事实。