1. 描述编辑
在处理单元格报表时,难免会出现很多单元格之间的计算,我们通常可以使用DATESUBDATE函数来进行计算两个日期的时间差,不过有的时候有特殊情况需要精确到xx小时xx分钟xx秒,可以使用下面这种时间类型相加减的公式。
最终的效果如下:
2. 公式编辑
首先新建工作簿,在A2单元格中输入13:00:00、B2输入13:26:00
C2单元格输入小时差:HOUR(B2) - HOUR(A2)
D2单元格输入分钟差:MINUTE(B2) - MINUTE(A2)
因为小时差可能的大于0,所以E2单元格输入实际分钟差:C2 * 60 + D2;
F2单元格输入最终的时间差:TRUNC(E2 / 60) + "小时" + MOD(E2, 60) + "分钟";
模板样式如下:
1)如果时间格式不是标准的hh:MM:ss格式,可用以下公式处理:
在A4单元格中输入13:50、B4输入14:20
C4单元格输入小时差:HOUR(FORMAT(TIME(INDEXOFARRAY(SPLIT(B4, ":"), 1), INDEXOFARRAY(SPLIT(B4, ":"), 2), 00), "HH:mm:ss")) - HOUR(FORMAT(TIME(INDEXOFARRAY(SPLIT(A4, ":"), 1), INDEXOFARRAY(SPLIT(A4, ":"), 2), 00), "HH:mm:ss"))
D4单元格输入分钟差:MINUTE(FORMAT(TIME(INDEXOFARRAY(SPLIT(B4, ":"), 1), INDEXOFARRAY(SPLIT(B4, ":"), 2), 00), "HH:mm:ss")) - MINUTE(FORMAT(TIME(INDEXOFARRAY(SPLIT(A4, ":"), 1), INDEXOFARRAY(SPLIT(A4, ":"), 2), 00), "HH:mm:ss"))
因为小时差可能的大于0,所以E4单元格输入实际分钟差:C4 * 60 + D4;
F4单元格输入最终的时间差:TRUNC(E4 / 60) + "小时" + MOD(E4, 60) + "分钟";
模板样式如下:
针对于特殊情况,主要是把时间类型格式化,然后再做处理,最重要的公式还是基本的时间公式HOUR(),MINUTE()基础公式;
2)另外,如果时间格式为yyyy-mm-dd hh:MM:ss,还需要对天数进行处理,转化成小时:
在A6单元格中输入2017-01-20 13:30:30、B6输入2017-01-22 11:19:20
C6单元格输入小时差:TRUNC((DATETONUMBER(B6) - DATETONUMBER(A6)) / 1000 / 60 / 60) + "小时"
D6单元格输入分钟差:TRUNC((DATETONUMBER(B6) - DATETONUMBER(A6)) / 1000 / 60) - TRUNC((DATETONUMBER(B6) - DATETONUMBER(A6)) / 1000 / 60 / 60) * 60 + "分"
E6单元格输入秒数差:if(right(B6, 2) - right(A6, 2) > 0, right(B6, 2) - right(A6, 2), right(B6, 2) - right(A6, 2) + 60) + "秒"
F6单元格输入最终的时间差:C6 + D6 + E6;
模板样式如下:
同时我们也可以使用一些其他公式来获取时间值,例如:
小时差:DATESUBDATE(B6, A6, "h") + "时"
分钟差:DATESUBDATE(B6, A6, "m") - DATESUBDATE(B6, A6, "h") * 60 + "分"
秒数差:DATESUBDATE(B6, A6, "s") - (DATESUBDATE(B6, A6, "m") - DATESUBDATE(B6, A6, "h") * 60) * 60 - DATESUBDATE(B6, A6, "h") * 60 * 60 + "秒"
如果感觉公式嵌套太繁琐,可以直接自定义函数,计算时间差即传递2个时间段直接相加减。
DateDiff函数源码:
2)注册自定义函数
编译完成后,选择服务器>函数管理器,添加DateDiff函数,如下图所示:
3)使用自定义函数
新建模板,给单元格添加公式DateDiff(TODATE("2017-01-20 13:30:30"),TODATE("2017-01-22 11:19:20"),"h{2}小时m{2}分s{2}秒"),使用方法如下: