时间类型计算

编辑
文档创建者:Poseidon (65718 )     浏览次数:2381次     编辑次数:9次     最近更新:jiangsr 于 2018-07-25     

目录:

1. 描述编辑

在处理单元格报表时,难免会出现很多单元格之间的计算,我们通常可以使用DATESUBDATE函数来进行计算两个日期的时间差,不过有的时候有特殊情况需要精确到xx小时xx分钟xx秒,可以使用下面这种时间类型相加减的公式。

最终的效果如下:

222

2. 公式编辑

2.1 简单的时间计算

首先新建工作簿,在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) + "分钟"

模板样式如下:

222

2.2 特殊格式处理

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) + "分钟"

模板样式如下:

222

针对于特殊情况,主要是把时间类型格式化,然后再做处理,最重要的公式还是基本的时间公式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

模板样式如下:

222

同时我们也可以使用一些其他公式来获取时间值,例如:

小时差: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.3 自定义函数

如果感觉公式嵌套太繁琐,可以直接自定义函数,传递2个时间段直接相加减,公式DateDiff()的使用方法如下:

222

DateDiff函数源码:

package com.func; import com.fr.script.AbstractFunction; import com.fr.stable.Primitive; import java.util.Date; import java.util.regex.Matcher; import java.util.regex.Pattern; public class DateDiff extends AbstractFunction { private static final long serialVersionUID = -2863679010825725885L; public String format = null; private Long dif = Long.valueOf(0L); public Object run(Object[] paramArrayOfObject) { try { Long localLong1 = Long.valueOf(((Date)paramArrayOfObject[0]).getTime()); Long localLong2 = Long.valueOf(((Date)paramArrayOfObject[1]).getTime()); this.dif = Long.valueOf(localLong2.longValue() - localLong1.longValue()); this.format = ((String)paramArrayOfObject[2]); replace("((?i)y)", Long.valueOf(31536000000L)); replace("((?i)q)", Long.valueOf(7776000000L)); replace("M", Long.valueOf(2592000000L)); replace("((?i)w)", Long.valueOf(604800000L)); replace("((?i)d)", Long.valueOf(86400000L)); replace("((?i)h)", Long.valueOf(3600000L)); replace("m", Long.valueOf(60000L)); replace("((?i)s)", Long.valueOf(1000L)); try { return Long.valueOf(Long.parseLong(this.format)); } catch (Exception localException2) { return this.format; } } catch (Exception localException1) { } return Primitive.ERROR_VALUE; } public void replace(String paramString, Long paramLong) { Pattern localPattern = Pattern.compile(paramString + "\\{\\d*?\\}"); Matcher localMatcher = localPattern.matcher(this.format); int i = (int)(this.dif.longValue() / paramLong.longValue()); int j = 1; while (localMatcher.find()) { String str1 = localMatcher.group(); str1 = str1.replaceAll(paramString + "\\{", ""); str1 = str1.replaceAll("\\}", ""); int k = Integer.parseInt(str1); String str2 = String.format("%0" + k + "d", new Object[] { Integer.valueOf(i) }); if (j != 0) { j = 0; this.dif = Long.valueOf(this.dif.longValue() - i * paramLong.longValue()); } this.format = this.format.replaceFirst(paramString + "\\{\\d*?\\}", str2); } } }

注:原目录没有func包,直接在com下新建立个func文件夹即可,然后DateDiff.class放在这个func文件夹下面。

附件列表


主题: 专题总结
如果您认为本文档还有待完善,请编辑

文档内容仅供参考,如果你需要获取更多帮助,付费/准付费客户请咨询帆软技术支持
关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

此页面有帮助吗? [ 去社区提问 ]