> 馆藏中心

【原】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的我爱极客的小胖,欢迎你在下方留言,把你遇到问题告诉我们,我们看到后,会第一时间回复。在工作的路上你并不孤单,我们一起同行!)

赞赏 共11人赞赏

excel函数计算加班时间

举例说明。例如有数据表如图:第一步:在E2输入公式:=ROUND((IF(OR(WEEKDAY(B2,2)=6, WEEKDAY(B2,2)=7),0,D2-C2-9/24))*24,1),然后拖动至E25.第二步:在F2输入公式...

月底如何用excel函数公式统计加班时间

=IF(D2>TIMEVALUE("21:00"),ROUND(48*(D2-TIMEVALUE("21:00")),)/48,) 就适用了.要计算本月的时间合计,如果本月是六月,公式为 =TEXT(...

EXCEL中函数算加班时间

设数据简略如图:将公式=TEXT(IF(B2<A2,B2+24-A2,B2-A2),"h:m")复制后粘贴到"C2"单元格,对准右下角,光标成"+"状时双击,效果如图: ...

excel text函数的详细的具体用法是什么?

4、如图,使用text函数把时间转化为自己想要的格式。5、如图,求加班时间,然后把时间转化为自己想要的格式。6、如图,求当前日期是星期几,Format_text为dddd,这个记住就行...

EXCEL如何使用函数加减加班时间

如果是带日期的时间,则直接相减即可:=TEXT(B1-A1,"[h]:mm")如果不带日期,则:=MOD(B2-A2,1)

如何用Excel计算加班时间,非常感谢

假设A1是下班时间,B1是员工工资,C1是加班工资,则在C1这样写: =IF(A1>TIMEVALUE("17:00"),(A1-"17:00")*1.5*B1,0) 当然,你必须根据实际情况把公...

求用Excel计算加班时间和迟到时间的公式

>=18,"加班","未加班"))) H2 =TEXT(IF(OR(G2="",G2="未加班"),"",(IF(WEEKDAY(C2)>5,TEXT(TEXT(C2,"h...

Excel考勤表里输入上下班时间后如何用函数自己生成加班时数...

提取18点以后一直到结果的时间就是加班的时间 假如加班结束时间为23:50在C2单元格,D2是加班时间,那么D2=C2-TEXT("18:00","HH:MM")

用excel怎么算加班时间 if

如图用excel表格编制员工的上班和下班时间,由此来计算员工的加班时间长短。 在需要显示加班时间的地方写上公式函数的前半部分,如图所示“=TEXT(”。公式要在英文状态...

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。本文《【原】Excel案例精解:TEXT函数巧算加班时间换来的约会》由网友我爱极客 转载收藏,版权归原作者所有,
违法违规信息请立即联系本网可获得现金奖励,

www.wosoni.com false 互联网 http://www.wosoni.com/view/socangkudk/mkdm/dm/czlszgjjWSNlcmdckcsk.html report 21872

热门图片

经济金融企业管理法律法规社会民生科学教育降生活体育运动文化艺术电子数码电脑网络娱乐休闲行政地区心理分析医疗卫生