996和955到底差了多少小时,你会算吗?【Excel教程】

来源:互联网 作者:老菜鸟

什么是996呢?是指早上9点上班,晚上9点下班,中午和晚上各有1小时吃饭时间,每周工作6天的工作模式。

而955,也就是朝九晚五,中午就餐在工作时间内,但不能休息,共计8小时工作时间,每周五天的工作模式。

那么问题来了,996一年比955多工作了多少小时?

不算不知道,一算吓一跳,竟然多了1042小时,这可是半年的工作量呢!!!

咱们今天不讨论996是否有问题,而是讨论怎么用Excel计算出相差的工时数,从而挖掘一些关于时间计算方面的技巧,至少以后遇到类似问题时可以少走弯路。


1

直接相减计算时间差



 

如果不考虑休息时间的话,要计算工时可以直接用下班时间-上班时间,如果还要在工时后面增加“小时”两个字,就需要用到TEXT函数。公式为=TEXT(C2-B2,"h小时"),操作过程看动画演示。

动画1

在这个公式中,用到了表时间的格式代码“h”,表示取小时数。在代码后可以自己添加需要显示的单位。


2

数字和时间之间的换算


如果要在工时中扣除休息时间,只需要在原工时中减去休息时间即可,但公式结果却出现了错误:

温馨提示:加入下面QQ群:885401280,下载教程配套的课件练习操作。

这里有一个问题,上班时间和下班时间数字是时间格式,而休息时间数字却是数字格式。在EXCEL中,一个自然数对应一个日期,而0到1之间的小数则对应一个时间。在本例中,休息时间是自然数,可以直接参与日期计算但不能参与时间计算,它需要进行转换,才能与时间格式的数字进行运算,转换方法是将这个数除以24。所以正确的公式应该是=TEXT(C2-B2-D2/24,"h小时"),结果如图所示:

注意:这里有一个坎,很多对EXCEL时间不了解的人会对C2-B2-D3/24心存疑惑。他们认为D3/24是一个小数,C2-B2=21-9=12,后12减去一个小数怎么就等于10呢?实际上,EXCEL并非按21-9=12这么来计算时间的。9:00或者21:00只是显示给我们看的时间格式数字,它们对应的真正的时间值是0.375(9/24)、0.875(21/24)。C2-B2-D3/24=0.875-0.375-0.08333=0.41667。最终时间值0.41667再显示为时间格式,就成了0.41667*24=10小时。

如果绕不过弯,那你也可以直接把休息时间的“2”改为“2:00”,则可以直接使用=TEXT(C2-B2-D2,"h小时")计算出工时。


3

计算排除周末的工作日天数



计算与工作日有关的问题时经常会用到NETWORKDAYS.INTL函数,如果要排除的休息日是固定的,在不考虑特殊节假日的情况下,只需要使用三个参数,函数格式为:NETWORKDAYS.INTL(开始日期,结束日期,周末)。996模式的工作天数可以用公式“=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11)”计算出来:

第三参数代码11表示仅星期日休息,在填写第三参数时,其他代码会有提示:

很明显,955模式只需要将第三参数改为“1”即可,表示星期六和星期日都休息。

这里需要说明的是,在公式中直接输入日期时,需要将日期放在引号中。

在本例中省略的函数的第四参数是除了周末的特殊节假日,可以单独输入,也可以直接引用单元格。

公式=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11,{"2018-1-1","2018-5-1","2018-10-1"})会在去掉周末的基础上排除元旦,五一和十一这三天:

也可以通过单元格区域进行排除:

公式为=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11,I2:I4)



4

防止小时发生进位 


工作天数有了,每天的工时有了,相乘即可得到全年工作时数,然而公式的结果却出乎预料:

是公式错了吗?

非也!

在公式“=TEXT(F2*(C2-B2-D2/24),"h小时")”中,F2是年度工作天数313天,“C2-B2-D2/24”表示每天工时10小时,10小时在EXCEL中对应的数字是0.4166666667,二者的乘积130.4166666667是一个带小数:

时间代码h只能显示数字小数部分对应的小时数(即0.4166666667对应的10小时),整数部分(130)对应的是日期就无法显示。

这种问题在对时间求和的时候经常会遇到,解决方法也很简单,给时间代码h加上一对中括号即可,正确的公式为:=TEXT(F2*(C2-B2-D2/24),"[h]小时")


5

对含有单位的数据进行计算



平时工作中经常会遇到在数字后面加单位的需求,看起来舒服,但要进行计算时就头疼了。正确的解决方法是使用自定义格式加单位,操作方法如动态演示:

动画2

使用自定义格式添加单位,数据格式并没有发生变化,所以不管求和还是其他计算,都很方便。

但有些时候已经填好了单位,再把单位去掉用自定义格式添加单位其实也挺麻烦的。以下通过计算带单位的996和955之间全年的时间差,介绍两个常用的公式。

 

公式1:通用法则


通用法则需要使用SUMPRODUCT和SUBSTITUTE这两个函数,原理是利用SUBSTITUTE将数据中的单位替换为空,再使用SUMPRODUCT对数组求和或求差,公式为:=SUMPRODUCT(SUBSTITUTE(G2:G3,"小时","")*{1;-1})&"小时"

SUBSTITUTE(G2:G3,"小时","")将单元格G2、G3中的单位“小时”替换为空,结果为{"3130";"2088"},是两个文本型的数字:

将两个文本型数字与常量数组{1;-1}相乘后变成一正一负的两个数{3130;-2088},同时完成了文本数值转为数字的过程:

最后使用SUMPRODUCT完成求和。

 

公式2:个性法则


个性法则是根据问题的具体特点设计的公式,在本例中,要计算的数据都是单元格的最左边4位,可以使用LEFT函数分别提取出来后进行相减,公式为:=(LEFT(G2,4)-LEFT(G3,4))&"小时"

这个公式相对很简单,也好理解,就本例而言是可行的,但不具有通用性。

今天分享了五个技巧,但其中涉及的细节知识点非常多,没有进行深入讨论,有不明白的点可以留言提问,共性问题会单独发表教程。

资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 现代古风通用PPT
  • 故宫国风演示文档PPT
  • 古风水墨简约模板PPT
  • 古风中国风环保主题PPT
  • 古风工作总结汇报模板PPT
  • 古风古典雅致通用模板PPT
  • 扁平插画手绘中小学生安全教育日PPT
  • 小学交通安全教育课件PPT
  • 小学交通安全教育课件PPT
  • 可爱卡通幼儿教学课件模板PPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2021 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。