【记录】Excel中的时间的特殊性导致TIME值变成介于0到1之间的值,不是我们想要看到的h:mm的形式

【问题】

之前折腾:

【记录】Excel内置函数实现两列的时间相减以及条件格式化

期间, 已经实现了计算出两列的时间差值。

但是后来证明,此结果,没有达到预期目标。

比如

一列是:8:31->8小时31分钟

另一列是:6.00 -> 6小时整,注意后面是小数点

计算出来的值,希望是:

8:31-6.00=2小时31分钟=2小时,31/60=0.517=2.517小时

但是实际上,此处用公式:

=TIMEVALUE(TEXT($H718,"h:mm")) - TIME(VALUE(MID($J718,1,1)),VALUE(MID($J718,3,2)),0)

计算出来的值却是:

0.105

效果如图:

calc tim diff is 0.105 not expected

【折腾过程】

1.后来注意到,6.00是x.xx其中xx是小数,而不是直接的分钟值,所以去修正公式为:

=TIMEVALUE(TEXT($H718,"h:mm")) - TIME(VALUE(MID($J718,1,1)),VALUE(MID($J718,3,2))*60/100,0)

结果依旧没变化:

change to 60 100 still is old value

 

(后来注意到,因为此处6.00中小数部分是00,所以结果00*60/100,肯定还是00,的确是应该没变化的)

2.后来经过折腾发现:

此处,通过

=MID($H718,1,4)

本来希望是得到的是:

8:31

但是实际上得到的却是:

0.35

(之前是:

=MID($H718,1,3)

对应着0.3)

如图:

h718 expect 831 but 035

3.所以,看出此处的值,有些异常。

然后,经过设置H718的格式为Text后:

831 format cell

set format to text

发现的确本身就是:

0.354861111

then value change to 0.35486 now

注:之前的格式是

h:mm

小时:分钟

的常见形式。

4.对此,很是迷惑。没搞懂。

后来的后来,是折腾,把当前的值,尝试使用到TIMEVALUE时,看到对应的提示:

timevalue note covert time to an excel serial number

即:

Convert a text time to an Excel serial number for a time,

a number from 0(12:00:00 AM) to 0.999988426(11:59:59PM).

Format the number with a time format after entering the formula.

然后才慢慢的明白:

此处的时间,内部本身所代表的值,的确是:

8:31

但是,却被excel,转换为对应的serial number了。

所以,实际上,此处的公式:

=TIMEVALUE(TEXT($H718,"h:mm")) – TIME(VALUE(MID($J718,1,1)),VALUE(MID($J718,3,2))*60/100,0)

中的:

前半部分:

TIMEVALUE(TEXT($H718,"h:mm"))

去使用TIMEVALUE将H718转换为时间的值的做法,本身就是错误的,多此一举的。

因为其本身就已经是TIME类的值了,就已经是介于0到1之间的那个serial number了。

所以,经过尝试,去改为:

=$H718 – TIME(VALUE(MID($J718,1,1)),VALUE(MID($J718,3,2))*60/100,0)

就可以得到,此处两者的时间的差值了:

但是还是

0.105

而不是2.xx的值:

first part to h718 but still is time serial

5.然后自己突然想起来,估计乘于24,就是所需要的值了。

然后去试了试,果然是的:

公式变成:

=($H718 – TIME(VALUE(MID($J718,1,1)),VALUE(MID($J718,3,2))*60/100,0))*24

结果变成所希望的

2.517

了,效果如图:

time diff multiply 24 got real value

 

【总结】

excel中的TIME所得到的值,不是我们所希望的,小时,分钟,时间的,而直接表示出来的值

而是此表示出来的值,除于24小时,而得到的,介于0到1(严格的说是0.999988426)

之间的小数,

比如上述的

2.517小时

实际上通过TIME计算后,就是:

2.517小时/24小时=0.104875=0.105了。

所以,如果想要对于显示出来的时间差值,excel中的time的serial number:0.105,变成2.517的话,就需要乘于24,才能得到2.517小时

感慨:

这么个做法,应该是便于excel计算了。

但是这样的概念,真的还是蛮坑爹的。



发表评论

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

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