中企动力 > 商学院 > excel计算天数公式
  • ?

    excel表格,如何计算日期的天数或时间差

    Shams

    展开

    在excel中如何单独计算日期的天数或时间差。对于日期和时间,之间的差,例如:日期时间差的公式,结果要小时(如134.5小时),比如2016年2月10日 13:26到2016年3月6日 10:40相距多少个小时,怎么实现呢?

    首先,单独计算两个日期之间的天数。

    这个很简单,两个日期直接相减就可以得到两个日期间隔的天数,如下图:

    也可以用隐藏函数DATEDIF函数来完成,输入:=DATEDIF(A2,B2,"d")

    这个函数语法是这样的:=DATEDIF(开始日期,结束日期,第三参数),第三参数可以有若干种情况,分别可以返回间隔的天数、月数和年数。如图所示:

    想要显示的结果有天数,比如:8天,这样的显示效果。可以通过这样实现:在以上的公式后边加上:&"天",=DATEDIF(A13,B13,"d")&"天",就可以了。

    那么,单据计算两个时间短之间的差。怎么实现呢?如图所示:

    对于日期和时间都有的情况下,求相差多少个小时?比如2016年2月10日 13:26到2016年3月6日 10:40相距多少个小时,怎么实现呢?

    输入=(B1-A1)*24,可以实现

    输入=TEXT(B1-A1,"[h]:mm")*24,也可以实现,利用函数text,是为了一个时间格式的转化。

    怎么加上“小时”显示,比如:8小时,这样的显示效果。可以通过这样实现:在以上的公式后边加上:&"小时",与上边讲述的一样。

    =ROUND(TEXT(B4-A4,"[h]:mm")*24,2)&"小时"

    介绍一下函数ROUND( ),为了保存几位小数,上述公式中的"2",就是保留两位小数的意思。

    ROUND(number, num_digits)

    number表示需要进行四舍五入的数值或单元格内容。

    num_digits表示需要取多少位的参数。

    通过学习和咨询,总计了上述的方法,可能显的有些啰嗦,无论怎样,方法和思维对了就可以。还得需要大家详细地具体操作。找到更多的,便捷的方法。

    一起学习和探讨,谢谢

  • ?

    看了这篇文章,你还不懂Excel日期时间的公式,那就是我的错

    姚烨磊

    展开

    铺天盖地的Excel公式分享,还是没能教会你Excel常用公式,为什么呢?

    这个问题,关系着你是否能慢慢开始自学Excel

    主要原因,我总结了几点:

    ★收藏的公式,都是知识碎片,看文章时一目了然,放下手机,一片空白

    ★大多数推文,都是以公式作为切入点讲解,但并没有实际工作中能用到的案例,把实操,讲成了理论

    ★很多文章中分享的公式,包括百度知道问答区的公式,都太过于高大上,以至于,我们零基础根本不明其意

    那针对以上问题,我最近会主要结合实践的工作,以工作中的实际案例为出发点来分享excel。如果案例中用到某个公式,我们再实际讲解。

    今天,我们就先来分享关于日期和时间类的应用。

    认清日期和时间的本质--不明白这点,不用再学公式了。

    日期的本质:今天的日期就是从1900年1月1日距今的天数。是的,日期的本质就是一个数字,这个数字是从1900年开始计算的天数。比如,今天(2018/6/13)就是43264这个数字。时间的本质:我们把一天划分成了24小时,也就是把它分成了24份。那么1点,就是1/24,约等于0.041,那么43264.041,大概就是今天早上的1点钟左右。

    我们来看实例:

    我们可以看到,A1的原型是个小数

    伪装成日期的文本

    看个例子:

    我随手找来了一个咨询工具导出的昨日对话记录。我们发现,AQ和AR列,都长的很像时间。

    但是,当我们将其格式调整成数值后,却没有显示出数字原型。也就是说,这不是真正的日期。

    这里需要注意的是,EXCEL中,隔断年月的分隔符,必须是“/”而“-”不能被识别为日期。

    而且,一定要注意的是,大多数系统(包括打卡机)导出的表格的日期,都是这种伪日期格式。

    伪日期转化成真日期

    因为我们可能需要按照日、周次、月份来统计数据,伪日期因为不是日期,所以无法计算周次的。

    所以建议养成一个习惯:拿到这种日期,第一时间格式化日期。方法很简单,直接看公式:

    --可以将文本格式的数字,转化为数字

    常用日期格式的转化(需要处理的日期放在A1单元格为例)

    日期时间提取日期:=int(A1);计算年份:=year(A1);计算月份:=month(A1);计算周次:=weeknum(A1,2);计算时:=HOUR(A1);计算分:=MINUTE(A7)

    weeknum的第二个参数

    解说:关于weeknum(),它有两个参数,其中,参数2,代表从周一开始计算为一周的第一天。若不填,则表示从周六开始计算一周的第一天。实际工作中,需要根据情况选择。在excel中输入该公式时会有中文提示,一看便知。

    常用的日期时间处理公式

    实际应用的建议

    如果你所统计的表的时间,包含或者将来可能会包含一年以上的时间,请使用【年-周】表示周次,请使用【年-月】表示月份!非常非常重要!

    具体公式建议这样书写:=year(A1)&"-"&text(weeknum(A1,2),"00")

    年周的计算公式

    我想,细心的朋友,已经已经明白了使用【年-周】的原因了吧!

    今天的分享,就到这里。朋友们可以在评论区留言分享自己的经验或提出需要小编帮忙解决的问题,我都会一一回复。同时,欢迎点赞关注【wesen】,感谢各位朋友的支持!

  • ?

    这个月有多少天?excel告诉你!

    封迎彤

    展开

    上小学时老师告诉我:1月、3月、5月、7月、8月、10月、12月的天数是31天;2月的天数是28天(闰年的2月是29天);4月、6月、9月、11月的天数是30天。

    上小学的时候,大表哥就因为记不住每个月有多少天被老师训导过。那么excel能不能自动计算出每个月的天数呢?大表哥告诉你是可以自动算出来的!

    语法

    EOMONTH(start_date, months)

    EOMONTH 函数语法具有以下参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。):

    Start_date 必需。一个代表开始日期的日期。应使用 DATE 函数输入日期,或者将日期作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。Months 必需。start_date 之前或之后的月份数。months 为正值将生成未来日期;为负值将生成过去日期。 注释 :如果 months 不是整数,将截尾取整。

    我们在B5表格里面输入公式DAY(EOMONTH(DATE($A5,1,1),0)),然后往右往左拉,即可得到如图效果。这个公司最重要的地方就是EOMONTH 函数可以算出每个月的最后一天,再配合day函数就可以算出每个月有多少天了。是不是SO easy!

    ---------------------------------------------------------------------------------------------------

    各位看官,小编一直想在EXCEL应用方面给大家解决一些问题,如果你的生活工作当中经常涉及到EXCEL应用,可以关注我们,当然如果有任何问题也欢迎评论区留言。我们会第一时间给大家解答。

    ---------------------------------------------------------------------------------------------------

  • ?

    Excel | EDATE计算合同到期日,DATEDIF计算距离到期日天数,并设置“交通三色灯”提醒

    Vicky

    展开

    每个公司都会有合同管理,做为HR管理人员,做一个自己一目了然的合同到期模板,是非常有必要的。

    韩老师今天讲一讲下图合同到期模板的制作。

    包括:

    根据合同签订日期与合同期限用EDATE函数计算合同到期日;DATEDIF计算距离到期日的天数,并设置“交通三色灯”提醒。

    关键操作根据合同签订日期与期限计算到期日

    在E2单元格输入公式“=EDATE(C2,D2)-1”。

    EDATE函数:

    语法:EDATE(开始日期,开始日期之前或之后的月份数)

    月份数为正值将生成未来日期;为负值将生成过去日期。

    计算距离到期日天数

    在F2单元格输入公式“=IF(E2>=TODAY(),DATEDIF(TODAY(),E2,"d"),"合同到期")”。

    用“三色交通灯”提醒到期天数

    选中“距离到期天数”一列,右键,选择“新建规则”:

    格式样式选择“图标集”,图标样式选择“三色交通灯”,设置根据数字类型显示各图标,同时输入数字范围:

    结果如下图:

  • ?

    7天,整理出常用的Excel函数公式大全(共21个)

    Porvoo

    展开

    在HR同事电脑中,经常看到海量的Excel表格,员工基本信息、提成计算、考勤统计、合同管理....看来再完备的HR系统也取代不了Excel表格的作用。一周前,交给小助理木炭一个任务,尽可能多的收集HR工作中的Excel公式。进行了整理编排,于是有了这篇本平台史上最全HR的Excel公式+数据分析技巧集。

    一、员工信息表公式

    1、计算性别(F列)

    =IF(MOD(MID(E3,17,1),2),"男","女")

    2、出生年月(G列)

    =TEXT(MID(E3,7,8),"0-00-00")

    3、年龄公式(H列)

    =DATEDIF(G3,TODAY(),"y")

    4、退休日期 (I列)

    =TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")

    5、籍贯(M列)

    =VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

    注:附带示例中有地址库代码表

    6、社会工龄(T列)

    =DATEDIF(S3,NOW(),"y")

    7、公司工龄(W列)

    =DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"

    8、合同续签日期(Y列)

    =DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

    9、合同到期日期(Z列)

    =TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]过期0天;[<30]即将到期0天;还早")

    10、工龄工资(AA列)

    =MIN(700,DATEDIF($V3,NOW(),"y")*50)

    11、生肖(AB列)

    =MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1)

    二、员工考勤表公式

    1、本月工作日天数(AG列)

    =NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

    2、调休天数公式(AI列)

    =COUNTIF(B9:AE9,"调")

    3、扣钱公式(AO列)

    婚丧扣10块,病假扣20元,事假扣30元,矿工扣50元

    =SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})

    四、员工数据分析公式

    1、本科学历人数

    =COUNTIF(D:D,"本科")

    2、办公室本科学历人数

    =COUNTIFS(A:A,"办公室",D:D,"本科")

    3、30~40岁总人数

    =COUNTIFS(F:F,">=30",F:F,"<40")

    五、其他公式

    1、提成比率计算

    =VLOOKUP(B3,$C$12:$E$21,3)

    2、个人所得税计算

    假如A2中是应税工资,则计算个税公式为:

    =5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

    3、工资条公式

    =CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")

    注:

    A3:标题行的行数+2,如果标题行在第3行,则A3改为A5

    工资数据源!A$1:工资表的标题行的第一列位置

    4、Countif函数统计身份证号码出错的解决方法

    由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。

    =Countif(A:A,A2&"*")

    六、利用数据透视表完成数据分析

    1、各部门人数占比

    统计每个部门占总人数的百分比

    2、各个年龄段人数和占比

    公司员工各个年龄段的人数和占比各是多少呢?

    3、各个部门各年龄段占比

    分部门统计本部门各个年龄段的占比情况

    4、各部门学历统计

    各部门大专、本科、硕士和博士各有多少人呢?

    5、按年份统计各部门入职人数

    每年各部门入职人数情况

    附:HR工作中常用分析公式

    1.【新进员工比率】=已转正员工数/在职总人数

    2.【补充员工比率】=为离职缺口补充的人数/在职总人数

    3.【离职率】(主动离职率/淘汰率=离职人数/在职总人数=离职人数/(期初人数+录用人数)×100%

    4.【异动率】=异动人数/在职总人数

    5.【人事费用率】=(人均人工成本*总人数)/同期销售收入总数

    6.【招聘达成率】=(报到人数+待报到人数)/(计划增补人数+临时增补人数)

    7.【人员编制管控率】=每月编制人数/在职人数

    8.【人员流动率】=(员工进入率+离职率)/2

    9.【离职率】=离职人数/((期初人数+期末人数)/2)

    10.【员工进入率】=报到人数/期初人数

    11.【关键人才流失率】=一定周期内流失的关键人才数/公司关键人才总数

    12.【工资增加率】=(本期员工平均工资—上期员工平均工资)/上期员工平均工资

    13.【人力资源培训完成率】=周期内人力资源培训次数/计划总次数

    14.【部门员工出勤情况】=部门员工出勤人数/部门员工总数

    15.【薪酬总量控制的有效性】=一定周期内实际发放的薪酬总额/计划预算总额

    16.【人才引进完成率】=一定周期实际引进人才总数/计划引进人才总数

    17.【录用比】=录用人数/应聘人数*100%

    18.【员工增加率】 =(本期员工数—上期员工数)/上期员工数

    【好东西记得分享哦!!!】

    【好东西记得分享哦!!!】

    【好东西记得分享哦!!!】

  • ?

    用Excel公式计算工资几分钟就搞定,是时候跟计算器说再见了

    拖泥

    展开

    有一个朋友,他有一个团队,做的是短期临时工作。每天的用工人数不是很固定,有时10来个人,有时20多人,高峰时期有30多人。他每天有一个工作,就是记录工人的考勤,一开始的时候,是记在本子上,每天都记,因为人员不固定,工作量特别大,特别是每2周核算工资的时候容易出错,当时按计算器,按了一会就不记得按到哪了。有时为了核对准确,要花好几个晚上,甚至熬夜到半夜。有一次他和我聊起他的烦恼后,我建议他立即使用Excel记录工资考勤,并利用Excel公式计算工人工资,让他从重复烦索的工作走出来,帮他节省了大量时间。那么用Excel公式计算工资是如何实现的呢?我现在就来演示整个过程。

    说明:以下为了表格不会太宽,以便在手机上观看最佳效果,只演示了1号至5号的工资情况(即C列是1号,D列是2号,以此类推,G列是5号),如增到2周或1个月只要在此基础上,在G列后面插入相应的列即可。

    第一步:左边红色线框中数据是考勤,根据实际出勤情况填写。1号时只要把C列(即1号)的出勤天数记录上,当天晚上收工回家花上几分钟记录一下。同理2号时把(D列)也是这样记录,当5天记录满时是以下左边红框内的数据。右边红框内的数据也是固定的,即每天的工资数。

    第二步,开始利用Execl公式,计算5天内的出勤总天数。用到的公式是=SUM(C3:G3),记得有个等于号=,SUM是求和意思,C3就代表C列和第三行交叉的数据,以下这种表示方法以此类推。=SUM(C3:G3)的表达的含义就是上图中0.5+0.5+1+1+1=4的结果。

    第三步:我们回车后就看到结果4出来了。

    第四步:选中H3单元格,出现十字光标时,往下拉。

    第五步:这样就得到H4的值是4.5,H5的值是5

    第六步,计算总工资时,用到的公式是=H3*i3

    第四步:回车时J3的值就出来了,是400。选中J3单元格,出现十字光标时,往下拉。

    第五步:这样就得到J4的值是315,H5的值是350

    第六步:计算汇总的公式是=SUM(J3:J6)

    第七步:回车后,汇总的工资就是1065

    至此,工资明细和工资汇总都出来,如果检查也很方便,检查一行一个人的,看看公式的范围有没有错,如果没有错基本就可以放心了。是不是Excel公式比用记在本子上,然后用记算器计算效率提升很多。您如果有需要也可以参考来做。

    本头条号:时代新生分享工作、生活、技能方面的经验,愿和您一起成长。请多多关注哦!

  • ?

    excel表格,如何计算日期的天数或时间差

    负荷

    展开

    在excel中如何单独计算日期的天数或时间差。对于日期和时间,之间的差,例如:日期时间差的公式,结果要小时(如134.5小时),比如2016年2月10日 13:26到2016年3月6日 10:40相距多少个小时,怎么实现呢?

    首先,单独计算两个日期之间的天数。

    这个很简单,两个日期直接相减就可以得到两个日期间隔的天数,如下图:

    也可以用隐藏函数DATEDIF函数来完成,输入:=DATEDIF(A2,B2,"d")

    这个函数语法是这样的:=DATEDIF(开始日期,结束日期,第三参数),第三参数可以有若干种情况,分别可以返回间隔的天数、月数和年数。如图所示:

    想要显示的结果有天数,比如:8天,这样的显示效果。可以通过这样实现:在以上的公式后边加上:&"天",=DATEDIF(A13,B13,"d")&"天",就可以了。

    那么,单据计算两个时间短之间的差。怎么实现呢?如图所示:

    对于日期和时间都有的情况下,求相差多少个小时?比如2016年2月10日 13:26到2016年3月6日 10:40相距多少个小时,怎么实现呢?

    输入=(B1-A1)*24,可以实现

    输入=TEXT(B1-A1,"[h]:mm")*24,也可以实现,利用函数text,是为了一个时间格式的转化。

    怎么加上“小时”显示,比如:8小时,这样的显示效果。可以通过这样实现:在以上的公式后边加上:&"小时",与上边讲述的一样。

    =ROUND(TEXT(B4-A4,"[h]:mm")*24,2)&"小时"

    介绍一下函数ROUND( ),为了保存几位小数,上述公式中的"2",就是保留两位小数的意思。

    ROUND(number, num_digits)

    number表示需要进行四舍五入的数值或单元格内容。

    num_digits表示需要取多少位的参数。

    通过学习和咨询,总计了上述的方法,可能显的有些啰嗦,无论怎样,方法和思维对了就可以。还得需要大家详细地具体操作。找到更多的,便捷的方法。

    一起学习和探讨,谢谢

  • ?

    Excel115 | 每月天数不一样,如何计算日平均销售量

    蝈蝈

    展开

    点击上方「 韩老师讲office 」可快速关注

    敬请阅读与分享,让更多学习者一起进步!

    问题来源

    前几天,韩老师讲过:Excel | SUMPRODUCT计算指定年份与月份的销售总额,今天,就有朋友传给韩老师数据,韩老师精简如下:

    计算每月的平均日销量,朋友问:

    1、难道用MONTH函数,确定月份,再用IF函数按照“一三五七八十腊,三十一天永不差”的规律去做?

    2、但遇到2月怎么办?难道还要确定年份是平年闰年?

    请韩老师支招!!

    公式实现

    在C2单元格输入公式:“=B2/DAY(EOMONTH(A2,0))”,即可得解。

    公式详解

    EOMONTH函数

    功能:

    返回某个月份最后一天

    语法:

    EOMONTH(start_date, months)

    中文语法:

    EOMONTH( 日期 , 月份数 )

    其中:

    Start_date即日期,必需,且必需是日期格式;

    Months即 月份数,必需,指日期之前或之后的月份数。 为正值将生成未来日期、为负值将生成过去日期、为0是本月。

    举例如下:

    DAY函数

    功能:

    返回日期所在月的天数,为从 1 到 31 之间的整数。

    往期相关推荐

    Excel114 | 按日期记录的产品销量,SUMIFS帮你按月统计

    Excel002 | 制作按年月自动变化的考勤表表头

    Excel | 一整年的缺勤表,统计出每月缺勤人数,还要排除重复项,怎么办?

    Excel | EDATE函数计算合同到期日,DATEDIF计算距离到期日的天数,并设置“交通三色灯”提醒

    Excel | 带薪年假,你可以休几天?

  • ?

    还在板着指头算每月工作天数?这个简单的Excel函数10秒钟搞定

    乔从凝

    展开

    要自动计算两个日期之间的法定上班天数,最常用的方法是用networkdays函数来做。

    案例1

    如下图所示,请计算一下开始日期与结束日期之间的法定工作天数?

    方法:直接在C7单元格输入公式:

    =networkdays(A7,B7)

    复制公式至C7:C21单元格区域即可。

    思路:此公式省略了networkdays函数的第3参数,也就是咱们只是去掉了周末,不考虑法定节日。

    如果我们要考虑除去法定节假日,Networkdays函数第3参数不能省略。下图右侧的表格是2018年的法定节日表,我们要得到实际的上班天数,

    方法:输入公式

    =NETWORKDAYS(A7,B7,$G$6:$M$12)

    思路:networkdays函数的第3参数为G6:M12单元格区域的日期,公式向下复制时这片区域不能变,因此使用绝对引用。

    案例1中省略第3参数与不省略第3参数的结果分别如下图所示:

    案例2

    如下图所示,请计算2018年2月的法定上班天数有多少天?

    方法:输入如下公式即可得到准确的结果

    =NETWORKDAYS("2018-02-01",EOMONTH("2018-02-01",0),$G$6:$M$12)

    思路:在networkdays及Eomonth函数中,直接输入日期需要加英文引号;由于不清楚2018年2月是闰月与否,我们直接使用Eomonth函数返回2018年2月的最后一天的具体日期。

  • ?

    怎么在excel中算过去某一天到今天的天数?

    Nekso

    展开

    两个日期之间一共过去了多少天可以把两个日期直接相减即可,在excel当中,today函数返回当天的日期,那么我们可以使用  =TODAY()-DATE(2016,12,4)  ,表示2016年12月4日到今天2016年12月5日过去了一天,如图:

    同理,比如我们要计算1988年12月4日距离今天过去了多少天,只需要输入公式 =TODAY()-DATE(1988,12,4)  返回结果是 10288天,如图

    (本文内容由百度知道网友huang1314wei贡献)

excel计算天数公式

所有视频需要登录后,才能观看

请先登录您的帐号,即可完整播放,如果您尚未注册帐号,请先点击注册。

img

在线咨询

建站在线咨询

img

微信咨询

扫一扫添加
动力姐姐微信

img
img

TOP