1. 帮助中心 >
  2. 博客 >
  3. 函数用法
返回

【函数】简道云函数用法:如何使用时间戳?

2016-01-04 标签:公式 函数 实例用法 

简道云中的日期时间控件,在计算机中的计算格式是“时间戳”。

什么是时间戳?如下图所示,在单行文本中直接对日期时间控件取值,得到的是13位的数字,这就是时间戳的表达形式。


时间戳是以毫秒为单位,以1970年1月1日世界时间0:00:00为0值的数值。早于这个时间的时间戳为负,晚于这个时间的为正。  说了这么多强调时间戳的目的是什么呢?我们计算日期时间的函数,是以时间对象为基础的,所以在对日期控件进行计算的时候,要先把时间戳转换成时间对象,再进行计算。


举例1:取出日期控件中的年月日,以XXXX-XX-XX的格式

方法1:

TEXT(DATE(),'yyyy-MM-dd'):直接把日期对象转化成指定格式的文本,其中yyyy表示年份,MM表示月份,dd表示日。


方法2:

要用到的函数:

DATE():把日期时间(时间戳)转换成时间对象

YEAR():取出时间对象中的年份

MONTH():取出时间对象中的月份

DAY():取出时间对象中的天数

CONCATENATE():连接函数,把分拆提取的年、月、日和短线符号“-”连接起来

公式为:

CONCATENATE(YEAR(DATE(时间日期)),'-',MONTH(DATE(时间日期)),'-',DAY(DATE(时间日期)))


上图中虽然日期取出正确,但是这样取出的文本在月地方,只有一位。如果需要统一成XXXX-XX-XX的格式,就要再套用RIGHT()与TEXT()函数:

RIGHT(a,b):从字符串a的右边取字符,取b个

TEXT():把数字格式变成文本格式。因为要对月和日补足位数,前面拼接一个0,所以取出的月和日需要先转成文本,否则直接用+号连接就变成了加法。

年份肯定是4位数,不用担心。月份和日期,可能会出现1位数或者2位数的情况,我们可以统一成2位数的格式:RIGHT()函数是从右边取位字符串的函数,只要在月份前面加一个0,从右边取2位即可:RIGHT('0'+TEXT(MONTH(DATE(时间日期))),2)。这个函数的意思是,假设月份为12,拼接1个0之后,012取右边2位依然是12;假设月份是5,拼接1个0之后,01取右边2位是05。这样就实现了月份取出必定是2位数的格式。日期同理。

最终的公式为:CONCATENATE(YEAR(DATE(时间日期)),'-',RIGHT('0'+TEXT(MONTH(DATE(时间日期))),2),'-',RIGHT('0'+TEXT(DAY(DATE(时间日期))),2))


举例2:两个时间之差


两个时间之差的计算,有一种最简单粗暴的计算方法,就是时间戳的运算。因为时间戳的单位是毫秒,因为两个时间直接相减得到的是毫秒差,然后根据需求换算成自己需要的单位。


日期1-日期2得到毫秒数,毫秒/1000得到秒,毫秒/1000/60得到分,毫秒/1000/60/60得到小时,毫秒/1000/60/60/24得到天,以此类推。


ROUND(a,b),对a四舍五入保留b位小数。


如果结果只有一个单位,如两个时间差多少小时,则可以直接写公式把毫秒数转换成小时,并保留2位小数:ROUND((结束时间-开始时间)/1000/60/60,2)



如果结果有多个单位,如两个时间差XX小时XX分,则先从大的单位开始计算,先将毫秒数转成小时并取整,再减去小时所包含的毫秒数并转换成分钟。

INT(数字),取数字中的整数部分,小数全部舍去。

先写出求小时数的公式:INT((结束时间-开始时间)/1000/60/60)



在求出分钟数,即时间差减去小时所包含的毫秒数,再转换成分钟,并取整:INT(((结束时间-开始时间)-INT((结束时间-开始时间)/1000/60/60)*60*60*1000)/1000/60)



最后把两部分的函数连接起来即可:CONCATENATE(INT((结束时间-开始时间)/1000/60/60),'小时',INT(((结束时间-开始时间)-INT((结束时间-开始时间)/1000/60/60)*60*60*1000)/1000/60),'分')