【记录】继续更新excel公式去支持:小时的输入有2,3,4三种位数,字符串比较是否相等,当J列为空输出J EMPTY

【背景】

之前已经实现了:

=($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

both holiday should calc out 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 and j2 both empty will null value

即:

H2和J2都是空值时,会计算失败,所以尝试去:

添加判断,当H2为空时,不计算,或者计算出值为0

关于判断单元格内容是否为空

参考:

“excel 判断单元格是否为空,如果不为空数值加一”

得知是:

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")

实现了效果:

when found j column empty show j empty

即:

除了上述计算差值之外,

当J列为空时,则显示 FOUND EMPTY

4.后来又遇到一个bug:

当J列内部值为一位小数点,比如是:8.5,时,实际上格式化后,显示出来的,是8.50

搞得以为本身的值就是8.50呢,导致,计算结果出错:

value 8.5 but show 8.50 calc diff is 0.7 hours

很明显,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")

计算出来效果如下:

for 3 num digit can also correct calc out real diff hours

即:

当长度为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),表示异常



发表评论

电子邮件地址不会被公开。 必填项已用*标注

无觅相关文章插件,快速提升流量