【背景】
之前已经实现了:
=($H158- TIME(VALUE(MID($J158,1,1)),IF(LEN($J158)>1, VALUE(MID($J158,3,2)), 0)*60/100, 0))*24
去计算:
两者的时间差。
具体参考:
【已解决】excel中用公式实现单元格内容的条件判断:根据单元格内容长度执行不同的逻辑
现在需要支持:
当两个是字符串时,判断是否相等,如果相等,则计算差值为0
比如:
两个都是Holiday,则希望的差值为0
【折腾过程】
1.所以需要:
去判断当前单元格,是否包含数字
参考:
最后用公式:
=IF(ISTEXT($H2),IF($H2=$J2,0,1000),($H2-TIME(VALUE(MID($J2,1,1)),IF(LEN($J2)>1,VALUE(MID($J2,3,2)),0)*60/100,0))*24)
当:
- H2是字符串时:Holidy/Day off/sick等,去判断H2和J2是否相等
- 如果相等:值为0 -> 表示填写时间和实际时间无差别
- 如果不相等:值为1000, -> 此处随便设置为1000,表示填写不准确
- H2不是字符串时:
- 还是用之前的计算公式:
- ($H2-TIME(VALUE(MID($J2,1,1)),IF(LEN($J2)>1,VALUE(MID($J2,3,2)),0)*60/100,0))*24
- 表示:
- 计算出时间差值,单位为小时。
2.不过,暂时还存在一个问题:
即:
H2和J2都是空值时,会计算失败,所以尝试去:
添加判断,当H2为空时,不计算,或者计算出值为0
关于判断单元格内容是否为空
参考:
得知是:
xxx <> "" |
最后,用公式:
=IF($J2<>"", IF(ISTEXT($H2),IF($H2=$J2,0,1000),($H2-TIME(VALUE(MID($J2,1,1)),IF(LEN($J2)>1,VALUE(MID($J2,3,2)),0)*60/100,0))*24), "FOUND EMPTY")
实现了效果:
即:
除了上述计算差值之外,
当J列为空时,则显示 FOUND EMPTY
4.后来又遇到一个bug:
当J列内部值为一位小数点,比如是:8.5,时,实际上格式化后,显示出来的,是8.50
搞得以为本身的值就是8.50呢,导致,计算结果出错:
很明显,8:46==8小时46分钟,和8.5小时==8小时30分钟,差值肯定没有0.7小时==42分钟。
所以需要去更新公式,支持:
当J列为x.x的格式的时候,也能正确计算差值
目前的情况是:
J列,有三种可能:
8
8.2
8.25
所以需要分别判断J列的当前长度
再去计算
=IF($J43<>"", IF(ISTEXT($H43),IF($H43=$J43,0,1000),($H43-TIME(VALUE(MID($J43,1,1)),IF(LEN($J43)=1,0,IF(LEN($J43)=3,VALUE(MID($J43,3,1))*10, IF(LEN($J43)=4, VALUE(MID($J43,3,2)),0)))*60/100,0))*24), "J EMPTY")
最后用如下公式:
=IF($J43<>"", IF(ISTEXT($H43),IF($H43=$J43,0,1000),($H43-TIME(VALUE(MID($J43,1,1)),IF(LEN($J43)=1,0,IF(LEN($J43)=3,VALUE(MID($J43,3,1))*10, IF(LEN($J43)=4, VALUE(MID($J43,3,2)),0)))*60/100,0))*24), "J EMPTY")
计算出来效果如下:
即:
当长度为3的时候,即8.5的时候
则拿到5,再乘于10,得到50
再去*60/100,得到50*60/100=30,单位才是分钟
否则,之前错误的算法是:
则拿到5,没有乘于10,只得到5,
再去*60/100==5*60/100=3,即才是3分钟,
所以和之前46分钟,差距了超过43分钟,大概是0.7小时了。
【总结】
最后,用对应的公式:
=IF($J2<>"", IF(ISTEXT($H2),IF($H2=$J2,0,"NOT SAME"),($H2-TIME(VALUE(MID($J2,1,1)),IF(LEN($J2)=1,0,IF(LEN($J2)=3,VALUE(MID($J2,3,1))*10, IF(LEN($J2)=4, VALUE(MID($J2,3,2)),0)))*60/100,0))*24), "J EMPTY")
可以实现:
1.当J列的时间值,是,一位的(8),3位的(8.2),4位的(8.25),都可以正确计算出时间差值
2.当J列为空时,输出: J EMPTY
3.当H列和J列都是字符串时,判断两者字符串是否相等,如果相等,输出为0,否则输出为一个很大的数(比如1000),表示异常
转载请注明:在路上 » 【记录】继续更新excel公式去支持:小时的输入有2,3,4三种位数,字符串比较是否相等,当J列为空输出J EMPTY