excel计算日期时间差 这些Excel函数公式，很多人都在找！

很多人都知道Excel函数公式功能强大，

但是遇到问题就写不出公式了。

现在找不到了。

我今天给你带了15个

很多人都在寻找的Excel公式，

我已经为大家做好了未雨绸缪的准备。

看了感觉不错的，

记得好好看看底部，分享给朋友。

01

下图是某公司员工刷卡考勤的一些记录。日期时间组合后的数据根据B栏刷卡日期和C栏刷卡时间获取..

在D2单元格中输入下面的公式，复制到D10单元格中，得到日期和时间组合后的数据。

=B2+C2

02

计算故障处理时间

下图是某运营商宽带故障修复报告单的一部分，故障处理时间要根据C列接单时间和E列处理时间来计算..

在单元格F2中输入以下公式，并将其复制到单元格F6。

=INT((E2-C2)*1440)

一天有1 440分钟。要计算两个时间间隔之间的分钟数，只需从结束时间中减去开始时间，然后乘以1 440。最后用INT函数剔除计算结果中小于一分钟的部分，计算出时长的分钟数。

如果需要计算两个时间间隔之间的秒数，可以使用下面的公式。

=(E2-C2)*86400

一天有86 400秒，所以用结束时间减去开始时间来计算秒数，然后乘以86 400。

此外，使用TEXT函数可以以文本格式将两次之间的间隔返回为一个数字。

以下公式返回两轮之间的小时数。

=TEXT(E2-C2，"[h]")

以下公式返回回合之间的分钟数。

=TEXT(E2-C2，"[m]")

以下公式返回两次舍入之间的秒数。

=TEXT(E2-C2，"[s]")

03

计算员工的在职时间

下图是某企业员工加班出勤的一些记录。需要根据C栏打卡时间和D栏打卡时间计算员工的加班时间..

如果用公式“=D2-C2”来计算E2单元格中的时差，由于部分员工的出发时间是第二天凌晨，出发时间小于到达时间，两者相减得到负数，计算结果将是错误的。

正常情况下，员工值班时间不会超过24小时。如果打卡时间大于打卡时间，说明两次在同一天；否则，这意味着下班时间是第二天。

在单元格E2中输入以下公式，并将其复制到单元格E10。

=中频(D2 >；C2、D2-C2、D2+1-C2)

中频功能判断D2单元的时钟输入时间是否大于C2单元的时钟输入时间，如果条件成立，则从时钟输入时间中减去时钟输入时间。否则，下班时间加1得到第二天的时间，再减去工作时间。

该公式也可以简化为:

=中频(D2 >；C2、D2、D2+1)-C2

也可以借助MOD函数计算余数。

=MOD(D2- C2，1)

用D2电池的非工作时间减去C2电池的工作时间，然后用MOD函数计算结果除以1的余数，返回的结果是忽略天的时差。

04

计算员工技能评估的平均时间

下图是某企业员工技能鉴定表的部分数据。B栏是文字记录的员工运营时间，需要计算员工的平均运营时间。

将D2单元格格式设置为“时间”，然后输入

降低数组公式，按组合键，计算结果为“0:01:12”。

{= sum (-text ({"at 0 "，" at 0:00 " } &；B2:B10，

“h:m:s；；；！0"))/9}

因为B列的时间记录是文本内容，Excel

无法直接识别和计算。

使用字符串“{”0，“^ 0:0”}”和B2:B10

图13 -17技能考核平均由时间网格的内容连接，变成九行两列的内存数组“{”0:1:18，“0:0:1:18“；”0: 59", "0: 00: 59";…; "0: 1: 27", "0: 0: 1: 27"} ".

Excel将“0: 00: 00”的文本字符串识别为时间，仍然将“0: 00”、“0: 00”和“0: 00”的字符串识别为文本。

TEXT函数的第二个参数使用“h:m:s；；；！0”，时间样式字符串转换为“h:m:s”样式，非时间样式文本字符串强制显示为0。计算结果如下。

{"0:1:18","0";"0","0:0:59";…;"0:1:27","0"}

text函数计算的结果仍然是text，加上两个负号，即负数的负数为正数，通过减负运算将TEXT结果转换为时间序列值。

最后将sum函数的SUM结果除以总人数9，得到平均考核时间。

05

从混合内容中提取时间和日期数据

从考勤机导出的信用卡记录通常包含日期和时间。如下图所示，需要分别从C列和D列提取B列信用卡记录中的日期和时间。

由于时间和日期数据的本质都是序列值，所以同时包含日期和时间的数据可以视为带小数的数值。其中，整数部分是表示日期的序列值，小数部分是表示时间的序列值。

使用以下公式提取C2单元格中的日期数据。

=整数(B2)

=TRUNC(B2)

用INT函数或TRUNC函数提取a列的整数部分，结果是代表日期的序列值。

以下公式可用于提取D2单元中的时间数据。

=B2-INT(B2)

=MOD(B2，1)

用MOD函数计算A2单元格除以1的余数，得到A2值的小数部分，结果是代表时间的序列值。如果结果显示为十进制，单元格格式可以设置为时间。

此外，还可以使用TEXT函数提取日期和时间，下面的公式可以提取a列中的日期。

= - TEXT(A2，“e-m-d”)

格式代码使用“e-m-d”，即“年-月-日”。

下面的公式可以提取a列的时间。

= - TEXT(A2，“h:m:s”)

格式代码使用“h:m:s”，即“小时:分钟:秒”。

06

将英文月份转换为月份值

如下图所示，A列是英文的月份名称，需要转换成B列对应的月份值..

在B2单元格中输入以下公式，并将其复制到B10单元格。

= MONTH(A2 & amp；1)

使用连接线“&”将单元格A2与数值“1”连接，得到一个新的字符串“Apr1”，该字符串成为系统可识别的文本类型日期样式，然后使用MONTH函数提取日期字符串中的月份。

年、月和日函数都支持数组计算，并广泛用于按时间段进行统计汇总。

07

汇总特定时间段的销售额

下图是某公司2017年的部分销售记录表。A列是业务发生日期，D列是业务发生金额。需要计算上半年的业务总额。

您可以使用以下公式完成总结。

= SUMPRODUCT((MONTH(A2:A13))& lt；7)*D2:D13)

MONTH函数返回单元格A2:A13中日期数据的月份值，结果是:

{1;3;6;2;6;3;4;6;7;8;6;11}

因为要计算1-6月的总业务量，所以需要判断月值是否小于7。

使用“月(a2: a13)”

08

汇总指定年份和月份的销售额

下图显示了一家公司的部分销售表。营业日期分布在不同的年份，销量要按年、月汇总在G栏、H栏。

在G3单元格中输入以下公式，复制到G3:H14单元格区域。

= sum product((YEAR($ A $ 2:$ A $ 746)= G $ 2)*(MONTH($ A $ 2:$ A $ 746)= $ F3)* $ C $ 2:

$ c $ 746)“YEAR($A:$A6)= G”是指使用YEAR函数计算“$ A $ 2:$ A $ 746”单元格的年份，并确定它是否等于“G $ 2”单元格指定的年份值。

“MONTH($A:$A6)=$F3”部分意味着使用MONTH函数分别计算“$A:$A6”单元格的月份，并确定它是否等于“$F3”单元格指定的月份值。

将两组逻辑值相乘。如果对应的位置都是逻辑值TRUE，则相乘后的结果为1；否则，它返回0。

用“$C:$C6”单元格的销售额相乘，通过SUMPRODUCT函数返回产品的总和。

使用该公式时，需要注意使用不同单元格引用方法的变化。其中日期范围“$A: $A6”和销售范围“$C:$C6”是绝对引用，而年份条件的“G”使用列相对引用和行绝对引用，月份条件的“$F3”使用列绝对引用和行相对引用。

09

日期和时间数据的组合

下图是某企业新生产线设备安装调试计划表。每个项目的天数需要根据开始日期和结束日期来计算。

在D2单元格中输入以下公式，将单元格格式设置为普通，然后将其复制到D6单元格。

=天数(B2 C2)+1

公式也可以写成:

=C2-B2+1

在实际应用中，直接减去两个日期计算差异天数更方便。

10

如下图所示，将A2单元格的格式设置为自定义格式“Day 0”，用下面的公式返回当前系统日期是今年的那一天。

= TODAY-“1-1”+1

在Excel中以“月-日”的形式输入日期，系统默认将其作为当年。“TODAY()-”1-1“是指从系统当前日期减去今年1月1日，再加一天得到今年的日期。

同样，下面的公式可以用来计算今年有多少天。

="12-31"-"1-1"+1

如果公式引用了包含日期或时间的单元格，Excel可能会自动将公式所在单元格的格式更改为日期或时间，然后可以根据需要重新调整单元格格式。

11

确定指定日期是季度的哪一天

如下图所示，需要根据a列的日期计算出季度的日期。

在B2单元格中输入以下公式，并将其复制到B10单元格。

=COUPDAYBS(A2，“9999-1”，4，1)+1

此函数是一个财务函数类别，用于返回从付息期开始到结算日的天数。

该函数的基本语法如下。

COUPDAYBS(结算、到期、频率、[基础])

第一个参数结算是证券的结算日期；第二个参数到期是证券的到期日，可以写成任意大的日期序列值；第三个参数frequency使用4，这意味着每年的利息支付是按季度支付的。第四个参数依据使用1，这意味着日期是根据实际天数计算的。

在这个例子中，中年的利息支付次数是按季度支付的，因此单元格A2的日期所在的季度的利息支付期是该季度的第一天。公式以单元格A2的日期为结算日期。通过计算从季度的第一天到当前日期的间隔天数，结果增加1，指定的日期是季度的第一天。

12

下图是某公司新员工入职表单的部分记录。入职日期要根据入职日期和实习月数来计算。

在D2单元格中输入以下公式，并将其复制到D10单元格。

=EDATE(B2、C2)

EDATE函数使用B2单元格中的日期作为指定的开始日期，并返回C2单元格指定的月份之后的日期。

13

下图是某公司的部分店铺租赁表，需要根据租赁起止日期计算租赁月数。

如果在F2单元格中直接使用下面的公式来计算间隔月份，并将公式复制到F9单元格中，则会在某些单元格中得到错误的结果，例如图13-54中的F2、F3、F4和F8单元格。

=DATEDIF(D2，E2，“m”)

当使用DATEDIF函数计算间隔月份时，如果结束日期是当月的最后一天，并且开始日期的天数大于结束日期的天数，则计算结果将少一个月。

根据这个规则，可以在原始公式中添加判断条件。在G2单元格输入下面的公式，复制到G9单元格，公式会返回正确的结果。

=DATEDIF(D2，E2，“m”)+AND(DAY(D2))>;日(E2)，E2 =月(E2，0))”日(D2)>;DAY(E2)"用于判断开始日期的天数是否大于结束日期的天数。

“E2=EOMONTH(E2，0)”用于判断结束日期是否是当月的最后一天。

当开始日期的天数大于结束日期的天数，并且结束日期等于当月的最后一天时，“与”函数返回逻辑值“真”，否则返回逻辑值“假”。

最后，将DATEDIF函数的结果与AND函数返回的逻辑值相加。在四个运算中，逻辑值TRUE的函数等价于1，逻辑值FALSE的函数等价于0。如果两个条件同时成立，则相当于原公式+1，否则为原公式+0。

14

下图是某公司新员工的一些记录。需要根据入职日期计算员工本月的出勤天数。

在C2单元格中输入以下公式，并将其复制到C8单元格中。

=网络日(B2，月(B2，0))

EOMONTH(B2，0)用于计算员工受雇月份的最后一天。

NETWORKDAYS函数将雇用日期作为开始日期，将雇员受雇月份的最后一天作为结束日期，并计算两天内的工作日数。

在本例中，省略了第三个参数。在实际应用中，如果这个月还有其他法定节假日，可以用第三个参数排除。

15

判断评估等级

下图显示了员工评估评分表的一部分。F3:G6单元格区域为考核等级对照表。第一列已按等级升序排序。要求根据D栏的考核等级找出相应的等级..

在D2单元格中输入以下公式，并将其复制到D11单元格。

=VLOOKUP(C2，F $ 3:G $ 6.2)

省略VLOOKUP函数的第四个参数，以近似匹配方式返回查询值的精确匹配值或近似匹配值。如果没有找到完全匹配，则返回小于查询值的最大值。

C2单元格的62分没有列在对照表中，所以Excel在F列中查找最大值60小于62进行匹配，返回G列对应的“合格”等级。

希望这篇文章能帮到你！

记不住的话可以发朋友圈自己标注。

上图来自

北京大学出版社出版

Excel 2016函数和公式的应用