Excel日期函数应用01计算员工工龄【例6-1】计算员工工龄。“fl6-1日期时间函数(职工工龄).xlsx”中记录着13名员工的姓名和入职日期。利用日期与时间函数(TODAY、YEARFRAC、DATEDIF、DAY)和数学函数(ROUND),使用不同的方法,计算员工当年的工龄。素材和结果如图6-1所示。
计算员工当年的工龄参考步骤(1)输入当前日期。在D1单元格中输入显示系统当前日期的公式“=TODAY”。(2)利用ROUND和YEARFRAC函数,计算员工当年的工龄(方法1)。在C3单元格输入公式“=ROUND(YEARFRAC(B3,$D$1),0)”,并向下填充至C15单元格。(3)利用ROUND、DATEDIF和DAY函数,计算员工当年的工龄(方法2)。在D3单元格输入公式“=ROUND(DATEDIF(B3-DAY(B3)+1,$D$1-DAY($D$1)+1,"m")/12,0)”,并向下填充至D15单元格。说明(1)方法2的公式中利用DATEDIF函数计算员工当年工龄月份数除以12得到带小数的工龄(年),再利用ROUND函数进行四舍五入取整。(2)但是由于DATEDIF函数对日期是严格按照day计算日期相差值。对于员工的入职日期是12月31日,而当前日期是6月30日的情况,DATEDIF函数会少算1个月,所以必须利用日期所对应的月首日期进行计算。也即,公式“=B3-DAY(B3)+1”返回员工入职日期所在的月首日期,例如,“赵丹丹”入职日期所在的月首日期为;公式“=$D$1-DAY($D$1)+1”返回系统当前日期所在的月首日期,假设系统当前日期为,则其所在的月首日期为。此时,公式“=DATEDIF(B3-DAY(B3)+1,$D$1-DAY($D$1)+1,"m")”返回和两个日期相差的月份数22。而“=22/12”返回1.,“=ROUND(1.)”结果为2,即“赵丹丹”当年的工龄为2(年)。02职工加班出差补贴统计【例6-2】职工加班出差补贴统计。“fl6-2日期时间函数(加班出差).xlsx”中记录着4名员工的加班和出差情况,加班工资按照小时计算(加班时间不足一小时的按一小时计算)。假设加班时间不能超过24小时,而且不能跨越两天。请利用日期与时间函数(WEEKDAY、DATEDIF)、数学函数(SUM、SUMIF、SUMIFS、ROUND、ROUNDUP)、条件判断函数IF、逻辑函数OR以及数组公式,完成如下操作。结果如图6-2所示。(1)分别以标准时间格式“时:分:秒”、十进制数字格式(保留2位小数)以及小时为单位,统计每位员工的加班时长。(2)判断加班时间是星期几,并使用各种方法确定是否为双休日。(3)利用数组公式统计每位员工总的加班时长、双休日加班时长,并根据表格中的支付标准计算加班工资。(4)统计每位员工的出差总天数。(5)请利用数组公式计算该单位所支出的出差补助总费用。
职工加班出差费用统计结果参考步骤(1)统计每位员工的加班时长(标准时间格式“时:分:秒”)(方法1)。在D3单元格输入公式“=C3-B3,并向下填充至D12单元格。设置D3:D12单元格区域以时间格式显示。(2)统计每位员工的加班时长(十进制数字格式)(方法2)。在E3单元格输入公式“=ROUND((D3-INT(D3))*24,2)”,并向下填充至E12单元格。(3)统计每位员工的加班时长(小时为单位)(方法3)。在F3单元格输入公式“=ROUNDUP(D3*24,0)”,并向下填充至F12单元格。(4)判断加班时间是星期几。在G3单元格输入公式“=WEEKDAY(B3,2)”,并向下填充至G12单元格。(5)判断加班时间是否为双休日。在H3单元格输入公式“=IF(OR(G3=6,G3=7),"是","")”,并向下填充至H12单元格。(6)利用数组公式统计每位员工加班总时长。在数据区域K3:K6中输入数组公式“{=SUMIF(A3:A12,J3:J6,F3:F12)}”。(7)利用数组公式统计每位员工双休日总加班时长。在数据区域L3:L6输入数组公式“{=SUMIFS(F3:F12,A3:A12,J3:J6,H3:H12,"是")}”。(8)利用数组公式统计每位员工加班工资。在数据区域M3:M6输入数组公式“{=L3:L6*K10+(K3:K6-L3:L6)*K9}”。(9)统计每位员工出差总天数。在D16单元格输入公式“=DATEDIF(B16,C16,"D")”,并向下填充至D19单元格。(10)利用数组公式计算出差补助总支出费用。在E20单元格中输入数组公式“{=SUM(D16:D19*E16:E19)}”。说明(1)在D3单元格中,还可以利用以下公式统计每位员工的加班时长,并以标准时间格式“时:分:秒”显示。只是结果为文本型数据。=TEXT(C3-B3,"[h]:mm:ss")(2)在H3单元格中,还可以利用以下公式判断加班时间是否为双休日:=IF(G3>5,"是","")=IF(OR(G3={6,7}),"是","")03视频讲解04参考书籍《Excel数据分析超详细实战攻略-微课视频版》ISBN:9作者:江红
05精彩推荐Excel条件格式化应用(附视频)Excel 2013神奇的快速填充功能(附视频)实例|为作品添加版权数据保护——ODRL(开放数字版权语言)实例|OpenGL图形库——利用键盘控制场景漫游高效学C++|组合类的构造函数高效学C++|函数参数的引用传递和函数重载实例详解|Android缓冲区错误漏洞网络爬虫 | Ajax-hook人工智能经典案例 |
有经纪人的交易为什么选择HarmonyOS人工智能经典案例 | 九宫格游戏人工智能经典案例 | Shakey世界鸿蒙开发实例|构建轻量级智能穿戴设备用户界面CCF CSP-J/S第一轮认证必考知识点:回溯算法Python 韩信点兵思政案例(含优惠码)机器学习案例︱人脸识别和人脸检测(附视频)Python
︱爬取天气预报信息(附视频)《机器学习》实验指导书(附实验参考+代码)Python爬虫综合实战 │ 创建云起书院爬虫(附代码)Python爬虫实战 │ Email提醒(附代码)Python深度学习 │一文掌握卷积神经网络Excel条件格式化应用(附视频)Excel
2013神奇的快速填充功能(附视频)实例|为作品添加版权数据保护——ODRL(开放数字版权语言)实例|OpenGL图形库——利用键盘控制场景漫游高效学C++|组合类的构造函数高效学C++|函数参数的引用传递和函数重载实例详解|Android缓冲区错误漏洞网络爬虫 | Ajax-hook人工智能经典案例 | 有经纪人的交易为什么选择HarmonyOS人工智能经典案例 |
九宫格游戏人工智能经典案例 | Shakey世界鸿蒙开发实例|构建轻量级智能穿戴设备用户界面CCF CSP-J/S第一轮认证必考知识点:回溯算法Python 韩信点兵思政案例(含优惠码)机器学习案例︱人脸识别和人脸检测(附视频)Python ︱爬取天气预报信息(附视频)《机器学习》实验指导书(附实验参考+代码)Python爬虫综合实战 │
创建云起书院爬虫(附代码)Python爬虫实战 │ Email提醒(附代码)Python深度学习 │一文掌握卷积神经网络