2019独角兽企业重金招聘Python工程师标准>>>
在日常工作中,我们可能需要对Excel表格对日期及时间进行整理和计算。一些简单的int型(number、数字)四则运算等我这里就不多解释了,只要读过小学和用过Excel的应该都能理解。今天要说的是一些利用公式的日期时间计算。希望这个能够帮到那些整天需要表格数据处理的朋友们。本案例变通后同样适用于HR考勤、财会、仓储等管理哦。
案例:已知一艘海轮停靠在某港区进行装卸作业,靠泊时间为:2011-12-31 07:45,离泊时间为:2011-12-31 23:10。作业开工时间:2011-12-31 09:30,作业完工时间:2011-12-31 13.45。此海轮的长度为:173米,海轮停靠的安全尺码间隔为:0.2米。请问:1、这艘海轮的累计开靠时间是多少?(要求显示格式为:天,小时,分钟)2、累计作业时间是多少?(要求显示格式为:天,小时,分钟)3、海轮对泊位停靠累计占用米时是多少?(精确到2位小数)4、海轮对泊位作业占用米时是多少?(精确到2位小数)提示:海轮对泊位的占用米时:∑((累计开靠时间)×船舶长度×(1+安全距离系数)),海轮对泊位的作业占用米时:∑((累计作业时间)×船舶长度×(1+安全距离系数))
更具要求,我们先建立Excel数据表格如下:
A | B | C | D | E | F | |
1 | 尺码 | 安全尺码 | 靠泊时间 | 开工时间 | 完工时间 | 离泊时间 |
2 | 173 | 2011-12-31 07:45 | 2011-12-31 09:30 | 2011-12-31 13.45 | 2011-12-31 23:10 |
解答:
1、海轮累计开考时间:
假设将结果存放在G2单元格内:
G2=TEXT((F2)-(C2),"D天H小时M分钟")
2、海轮累计作业时间:
假设将结果存放在H2单元格内:
H2=TEXT((E2)-(D2),"D天H小时M分钟")
3、海轮对泊位停靠累计占用米时:
之前我们已经算出累计开考时间,但是结果是以TEXT的文本形式储存的,对于Excel而言,文本与数字不能进行数学或逻辑运算,所以我们要将累计开靠时间转换成双精度浮点数(即保留两位小数的数字),并保存在I2单元格内。我们用到如下公式,
I2=TEXT((F2)-(C2),"[H]")+MINUTE(G2)/60
那么,米时=时间*安全尺码,即:
I2=(TEXT((F2)-(C2),"[H]")+MINUTE(G2)/60)*(A1+A1*0.2)
注:重新计算累计开考时间,并提取小时+分钟
4、海轮对泊位作业累计占用米时:
同理,J2=TEXT((E2)-(D2),"[H]")+MINUTE(H2)/60*(A1+A1*0.2)
扩展阅读:
将N个单元格的数字合并成一个单元格数据:
=CONCATENATE(N1,N2,N3...)
将四位数字转化为时间格式,例如:0735 => 07:35
=TEXT(N1,"00\:00")