打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel案例精解:TEXT函数巧算加班时间换来的约会

小陆正在网上查一些关于精致生活的用品的时候,突然觉的左肩一沉,转头一看没人,正想继续时,右肩又一沉,正想发火说:谁啊,这么讨厌,打扰人家工作!可看到是谁后,瞬间笑脸相迎说:霉霉,什么事啊,你看看你有什么想要吗?我买给你啊!

霉霉:别说买东西了,哪有心情啊,领导又给我出难题啦!

小陆:开始都这样,走,去你哪吧,边走边说是什么问题吧?

霉霉:嗯,领导让我统计员工的总的加班时间?要求<30分,不算加班,>=30且<60的算30分钟,>=60且<90的算60,依次类推,这怎么计算啊?

小陆:我给你讲的日期的部分的函数,也没有系统的给你说日期的时间的知识,你不会很正常啊!打开你的发给你的表格,让我看看都是有什么内容?

霉霉打开了发来的表,内容如下:


公司员工的考勤信息表

小陆一边在E列创建的加班时间,接着输入公式:=ROUND(FLOOR(D2-(TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30")/TEXT("1:00:00","0.000000"),3),然后拖拽完成公式填充。

霉霉:这个公式里我们就认识3,4个函数,其他的函数都是干什么的?之前你的写的吧,我还能看明白,今天的这个公式彻底蒙圈啦!你赶紧跟我讲讲吧。说着霉霉已经拿出小本本,准备好记笔记啦!

小陆:我先简单说一下思路:咱们正常的下班为17:30,我们用下班打卡的时间减去正常下班的时间,求出时间差,然后与30分比较,是30分钟整倍数的返回整倍数时间,然后再将分钟转化成小时数(方便日后求计算加班费)。

霉霉连连点头:嗯嗯,公式中我唯一能看懂就是D2使用引用的下班打卡的时间,其他就看不懂,你仔细讲解一下公式吧。

小陆:TEXT函数的功能是将字符串按设定的格式编码显示,我使用这个功能只截取了打卡的当天日期然后拼接下班时间,组成当天的下班日期时间串,注意在拼接时间的时候,一定要在时间字符串前加个空格,否则会导致无法识别为日期时间格式,也就无法计算,然后用打卡时间与之相减,得出加班时间。

TEXT文本函数使用语法结构图

霉霉疑惑的说:哪(TEXT(D2,"yyyy/m/d")&" 17:29:59")最终会得出什么结果呢?

小陆:执行后的结果:2019/6/1 17:29:59,时间日期字符串,这个结果可以直接与日期格式计算的。

霉霉:为什么是17:29:59啊?不应该是17:30:00吗?

小陆:这个跟公式中的floor函数有关,它的功能是向下取整,它有两个必填参数,第一个处理的数字,第二个参数为倍数,只保留最大整倍数的数字,比如floor(1,0.3)=0.9、语法结构图如下:

FLOOR函数语法结构图

正是利用floor这个特性,正好符合公司定的30分钟的规则,所以公式就可以这么写:=floor(D2-TEXT(D2,"yyyy/m/d")&" 17:30:00"),"0:30:00"),会出现18:00:00打卡时,公式计算为0,为了纠正需要将公式变为=floor(D2-TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30:00"),这个问题可以调整也可以不调整,因为打卡的时间不太可能整分整秒。

霉霉:哪是为什么呢?怎么会出现这种情况呢?

小陆:由于时间30分是个无限循环的小数,因为Excel的精度问题,不过还好精度误差在1-2秒,这也是我为什么将公式提前1秒就能解决精度导致的误差问题,最后我们需要将结果转化成小时并保留2位小数即=round(floor(D2-TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30:00")/text("1:00","0.000000"),2),最后加一个严谨性判断iferror就搞定啦!

霉霉:round函数也是小数保留函数吗?

小度:没有错,它是符合四舍五入的规则的,第二个参数就是保留几个小数位标志。

ROUND函数使用语法结构图

一个爱讲,一个爱听,不知什么两人背后多了两个人都不知道,不用说你也猜到了,正是小皮和小度。

小度接着说:陆儿啊,你的公式有问题,你怎么能舍近求远呢,计算时间还要带上年月日呢,者只需要计算小数部分就好啦,看我的:=ROUND(FLOOR(MOD(D4,1)-TEXT("17:30:00","0.000000"),"0:29:59")/TEXT("1:00","0.000000"),2),怎么样,这回我的写法没有问题吧!

小陆:嗯,是没有问题,我是因为怕将太多了,霉霉记不住,所以没有讲这种方法。然后转过头去,问小皮:你是不是也有什么要说的啊?

小皮:当然,我看见你俩吧,公式写的不错,就是操作太初级了,怎么还拖拽填充公式呢?我来教你们一手,鼠标停到单元格右下角,当变成实心的+时,双击就能完成整列有内容部分的填充!

小陆笑了笑,小声告诉霉霉:赶紧记下来吧,这个不错!

霉霉:第二个公式我不太明白呢?

小陆:你先记下来,回来我给你再讲,今天讲的东西挺多了,你回去消化消化吧!

小皮小度都觉得很尴尬,小皮说:忘了忘了,我的文案还没有写完了!

小度说:嗯嗯,我也有点还没有弄,我得走了!

这两人就这样灰溜溜的走了,小陆说:等过这段时间,我好好给补补Excel啊!

霉霉:好啊好啊,哪是去我家,还是你家?此处省略1万字。

加班时间效果图

(我是喜欢用故事聊excel的我爱极客的小胖,欢迎你在下方留言,把你遇到问题告诉我们,我们看到后,会第一时间回复。在工作的路上你并不孤单,我们一起同行!)

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
Excel身份证号码提取出生年月日公式:Excel函数不求人
Excel教程:公历日期转农历的6个公式
Excel实战:合同到期统计表如何用year,month,day函数实现!
独特角度,全面剖析Excel隐藏函数DATEDIF功能
EXCEL中的TEXT函数详解
你会用 Text 函数吗?
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服