找出两次的差值，然后乘以一个值

I wish to calculate the difference between two times multiplied by a value in another column. My columns are like this:

我想计算两乘以另一列的值的差值。我的专栏是这样的:

       Column A                Column B             Column C
1    09:00 - 21:00                 2

I want my Column C to return 24, since the time difference in Column A is 12 hours, and the multiplier in Column B is 2.

我想让C列返回24，因为A列的时差是12小时，B列的乘数是2。

2 个解决方案

#1

Please try:

请尝试:

=B1*24*(MID(A1,FIND("-",A1)+2,LEN(A1))-LEFT(A1,5))

#2

Here is another approach which uses TIMEVALUE() in case your formatting around the hyphen includes whitespace.

这里是另一种使用TIMEVALUE()的方法，以防止在连字符周围的格式包括空格。

=(TIMEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1)))*24*B1

As stated in the comment up above, you will do better to have these in separate columns, but these formulas work nonetheless.

如上面的注释所述，您最好将这些放在单独的列中，但是这些公式仍然有效。

If you want to subtract times that are split across midnight (00:00), you can force clock arithmetic using the MOD(XXX, 24) formula. This will prevent negatives. It also means that the difference can never be greater than 24 (before multiplying by column B).

如果你想要减去在午夜被分割的时间(00:00)，你可以使用MOD(XXX, 24)公式强制时钟算法。这将防止底片。它还意味着差值不能大于24(在乘以B列之前)。

=MOD((TIMEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1)))*24,24)*B1

#1