【背景】
之前折腾过了:
但是现在遇到一个问题:
H2(以及同列的值),除了8.12之类的值之外,还可能出现8这样的值,所以公式就失效了。
希望可以去用公式实现条件判断,实现对应的时间差的计算。
【解决过程】
1.参考:
知道IF的写法:
Excel IF 函數公式說明: |
2.参考:
搞懂如何做逻辑判断
Formula =AND(A2>A3, A2<A4) =OR(A2>A3, A2<A4) =NOT(A2+A3=24) =NOT(A5="Sprockets") =OR(A5<>"Sprockets",A6 = "Widgets") |
3.参考:
How to read characters Count “Text Length” for Excel cell?
得知如何获得单元格内容长度:
=LEN(A1) Where A1 is the Cell whose text length you want. |
4.然后最后去用公式:
=($F2 - TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0))*24
得到要的效果。
(1)公式语法详解:
=($F2 – TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0))*24 H2单元格所表示的时间值 =TIME(小时,分钟, 秒) 小时 =VALUE( $H2单元格的内容,从第一个算起,长度为1个 ) 分钟 =IF(判断条件,条件为真时的值,条件为假时的值) 判断条件 =H2单元格的长度是否大于1 条件为真时的值 =VALUE(需要转换为数值的文本字符) 需要转换为数值的文本字符 =H2单元格中,从第三个算起,长度为2,的文本内容 条件为假时的值 秒 |
(2)公式含义解释:
完整含义: 然后乘于24,得到对应的以小时为单位的差值 其中的H2单元格的时间值: 或x,比如8 所以 获得小时的数字字符,可以直接通过: MID($H2,1,1) 而得到然后再通过: VALUE(MID($H2,1,1)) 将数字字符转换为数字的值 而获得分钟和秒的值,则需要用: IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0) 去判断: 当H2的值的长度大于1: LEN($H2)>1 时,即x.yz的形式时,才去用: VALUE(MID($H2,3,2)) 得到分钟所对应的数字值 否则,对于x,即8的形式,比如分钟是0,所以直接写为0 最后,将组合出来的,分别对应着,小时,分钟,秒的值: IF(LEN($H2)>1, VALUE(MID($H2,3,2)) 0 放到TIME中: TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0) 而得到H2所表示的时间的值 |
(3)最终效果为:
【总结】
excel中的函数,还是不错的。