人都需要上班，上班为了什么？为了赚钱生活，那么如何能快速计算规定以外的上班时间呢？ （为了更准确确定你的加班费）

安志斌制作

首先用IF函数，确定今天是不是礼拜天，如果不是礼拜天今天加班了几个小时？

=IF(B1="礼拜天",B2,B2-8)

解释： 如果B1单元格等于 礼拜天，那么B2单元格就等于B2，否则就用B2单元格减去8（法定的8个小时）

得出的效果如图二 B2单元格为8 ，【 8-8 】结果就是等于 0；

安志斌制作

安志斌制作

我们要计算的是一段时间，不是一天，所有把公式改为

=IF(B1:H1="礼拜天",B2:H1,B2:H1-8)

这个公式意思就是：

在这个区域内，重复执行公式=IF(B1="礼拜天",B2,B2-8)

现在这个公式已经输入数组公式了，不能正常的使用Enter结束了，要用【Ctrl+Shift+Enter】 三组合结尾了！（数组公式的结尾方法）

安志斌制作

安志斌制作

安志斌制作

接下来就是用到SUM函数求和了：

在原有数组公式：

=IF(B1:H1="礼拜天",B2:H1,B2:H1-8)的基础上加上 sum 即可！

组合公式： =SUM(IF(B1:H1="礼拜天",B2:H1,B2:H1-8))

意思是：

求这个区域内【礼拜天】全天 和 其它时间大于8 以外数值的和；

因为在添加SUM函数之前的公式为数组公式，所以还是需要使用【Ctrl+Shift+Enter】 三组合结尾了！

安志斌制作

安志斌制作

安志斌制作

这个时候如果直接下拉单元格，会发现数据计算结果不正确；

别着急，打开公式找原因！

发现数据源的位置随着公式的下拉也自动调整，那么现在用$$这个符号对公式位置进行固定！

可以手动添加$这个符号，也可以打开公式选中值 然后按F4 直接添加；

安志斌制作

安志斌制作

安志斌制作

安志斌制作

添加完毕后使用 【Ctrl+Shift+Enter 】 三组合结尾，下拉即可！

最后检查，可以看到结果是正确无误！

安志斌制作

安志斌制作

如果有不懂的可以直接在评论区留言~

想联系我的可以直接，私信！谢谢支持！

作者：安志斌

时间：2018-5-3

计算今年是否为闰年

公式

=IF(COUNT(-"2-29"),"是","否")

思路解析：

1、在Excel中如果输入“月/日”形式的日期，会默认按当前年份处理。

2、如果当前年份中没有2月29日，输入"2-29"就会作为文本处理。

3、如果当前年份没有2月29日，"2-29"前面加上负号，就相当于在文本前加负号，会返回错误值#VALUE!。

4、再用COUNT函数判断-"2-29"是数值还是错误值，如果是错误值，当然就不是闰年了。

计算今年有几天

="12-31"-"1-1"+1

前面说过，在Excel中如果输入“月/日”形式的日期，会默认按当前年份处理。

"12-31"-"1-1"就是用当前年的12月31日减去当前年的1月1日。

再加上一天，就是全年的天数了。

有朋友说，公式写成这样呢：

="2017-12-31"-"2016-12-31"

这样的话，公式有保质期，放到明年就不能用了，哈哈。

图文制作：祝洪忠

ExcelHome，微软技术社区联盟成员

上一次写了一篇关于EXCEL公式的文章，发现收藏的朋友占到了总阅读量的三分之一，这是其它类型文章所没有的，说明头条里对EXCEL公式应用感兴趣的朋友很多，收藏起来也是方便以后套用，所以本头条号以后会多多发表一些关于EXCEL公式的文章，同时，也希望朋友们不只是收藏起来给自己用，也可以多多分享出去让更多的朋友知道。

本次分享三个关于日期方面的公式应用。

1.资料到期日期的管理

可用到的部门：人事、采购、品管

我们在管理人员的劳动合同、供应商的协议书、检测报告时，这些资料有些公司设定了有效期，超过了期限需要重新签订或要找供应商重新索取。

如果我们能在即将到期前，就能重新签订或及时找供应商要新的报告，就可以避免临时去补资料，也便于档案管理。

有效期提醒

公式1：

B2栏=EDATE(A2,12)-1

这个是算有效期的，此处以一年有效。

公式2：

C2栏=IF((B2-NOW()-10)>=1,"有效期内",IF(AND(B2-NOW()>1,B2-NOW()<=10)=TRUE,"有效期在10天内","超过有效期"&INT(NOW()-B2)&"天"))

这里将提示设为三种状态，一种是在有效期内的，一种是有效期在10天内的（此时就需要去做资料更新的工作啦），一种是超过有效期10天以上的（时间天数越久，说明工作的滞后严重程度）。

里面用到的公式：

IF：属于逻辑函数，就是用来判定是否满足设定的条件的；

NOW()：属于日期函数，就是当前的时间；

TRUE：属于逻辑函数，表示满足设定的条件；

INT：属于数学函数，取数值的整数。

连起来看的意思就是：B2的日期减去现在的时间-10天，结果如果大于1，那么就显示在有效期内，如果B2的日期减去现在的时间大于1但同时小于10，那么显示有效期在10内，否则就显示现在日期减去B2的日期后所得的天数。

2.转换日期格式

我们很多表格从其它系统导入到EXCEL的时候，很多格式不一致，如何将这些格式转化为统一的日期格式，以便后续归类呢，下面总结了几种常见的格式进行转换。

日期转换

这里就不详细解说了，碰到相类似的，直接套用即可。

3.计算工作天数

做生产安排的朋友一般都要知道当月的工作天数，很多朋友在这时候会去翻台历，今天要告诉大家一种快速知道当月工作天数的方法，只要在B2栏输入一个日期（当年的），就可以知道该日期所在月份的工作天数（在B5栏显示，已减去F栏的公众假日）。

工作时间

动态图

EXCEL会让繁杂的工作变得简单，而且减少出错的机率，本头条号会经常分享日常工作用到的实用公式。

需要本文章EXCEL实例的朋友，可在头条内发私信给我。

日期函数在我们的日常生活中也是非常的常用的，如果能够熟练掌握，将会对你的工作效率有很大的提高。

一、Today函数：返回当前日期。

方法：

1、函数法：在目标单元格输入公式：=TODAY()。

2、快捷键法：Ctrl+；。

二、Now函数：返回当前日期和时间。

方法：

在目标单元格输入公式：=NOW()。

三、TEXT+MID：提取出生日期。

方法：

在目标单元格输入公式：=TEXT(MID(C3,7,8),"00-00-00")。

四、Datedif函数：计算年龄。

方法：

在目标单元格输入公式：=DATEDIF(D3,TODAY(),"y")。

解读：

1、Datedif函数是Excel的隐藏函数。其主要功能是计算两个日期之间相差的年数、月数、日数等。

2、参数y指定的类型为年。

五、Text函数：计算星期。

方法：

在目标单元格中输入公式：=TEXT(D3,"aaaa")。

六、显示周末日期。

方法：

1、选定需要设置的日期。

2、【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】。

3、输入公式：=weekday(d3,2)>5。

4、单击【格式】-【填充】，选取填充色，【确定】-【确定】。

解读：

公式=weekday(d3,2)>5中，当单数为2时，1、2、3、4、5、6、7分别代表星期一至星期天。如果计算出的日期（最终表现位数字）大于5，则填充为红色。

七、Text函数：计算上班时长 。

方法：

在目标单元格中输入公式：=TEXT(D3-C3,"H小时")。

八、合同到期提醒功能。

方法：

在目标单元格输入公式：=IF((C3-TODAY())<10,"合同快到期了","")。

解读：

1、如果两个日期的差值小于10天，则进行提醒“合同快到期了”。

2、如果大于等于10天，则返回空值。

九、计算还款日期。

方法：

在目标单元格中输入公式：=EDATE(C3,D3)。

       在excel中计算日期差，要用到excel数据库函数datedif，在excel中这个函数是隐藏的，它的函数语法是=（开始日期,结束日期，返回的信息类型），下表是模拟演示：

从左到右依次公式为

     =DATEDIF($A2,$B2,"y")&"年"

    =DATEDIF($A2,$B2,"m")&"个月"

    =DATEDIF($A2,$B2,"d")&"天"

    =DATEDIF($A2,$B2,"ym")&"个月"

    =DATEDIF($A2,$B2,"md")&"天"

    =DATEDIF($A2,$B2,"yd")&"天数"

下面是第三参数的具体解释：

    "Y" 一段时期内的整年数。   

    "M"一段时期内的整月数。

    "D"一段时期内的天数。

    "MD"start_date 与 end_date 之间天数之差。 忽略日期中的月份和年份。

     "YM"start_date 与 end_date 之间月份之差。 忽略日期中的天和年份

     "YD"start_date 与 end_date 的日期部分之差。 忽略日期中的年份。

如果要计算多少年多少月零多少天，如下图的公式：

（本文内容由百度知道网友清风吧吧啦贡献）

刚好有两个读者对获取日期间隔数有疑问，卢子今天就进行详细说明。

1.如何计算两个日期之间相差的天数？

日期其实就是特殊的数字，数字是可以进行加减运算，两个日期相差的天数就是当天的日期减去入库的日期。在运算的时候，计算出来的天数是以日期的形式显示，需要再将单元格设置为常规格式。

有的时候，我们用VLOOKUP函数查找日期，公式设置完后将单元格设置为日期格式。这时会看见1900/1/0这样的日期，怎么回事呢？

当查找的对应值是空白单元格，就会返回0，而0设置为日期格式就是1900/1/0，数字1的日期就是1900/1/1。

在Excel中的日期起点是1900/1/1，不允许日期在1900之前，比如1899/12/1其实不能算日期，只能算一个文本内容。如果你不信，可以用ISNUMBER函数判断，如果是数字就返回TRUE，否则就返回FALSE。

将日期设置为常规格式，就可以看到相对应的数字。

同理，时间也是数字，也可以进行四则运算。

不过时间在相加的时候，需要注意，当时间超过24小时的时候，运算会出错。一周上班总时间很明显不是4:45，针对这种问题又该如何解决？

嵌套一个TEXT函数，[h]就表示超过24小时显示实际小时数。

2.如何计算两个日期之间相差的月份和天数？

从上一个案例知道，日期是数字，可以直接进行四则运算。但是，问题来了，如果直接用四则运算，是没法判断两个日期相差几个月，因为每个月的天数是不固定的。

这时就出现一个DATEDIF函数，这个函数可以获取两个日期相差的年月日。这是隐藏函数，跟普通函数有所区别，在输入的时候没任何提示。即使你输入date也看不到任何关于这个函数的信息，所以当你输入函数的时候，找不到是很正常的。

计算两个日期相差的年月日，y代表年，m代表月，d代表日。

=DATEDIF(A2,B2,"y")=DATEDIF(A2,B2,"m")=DATEDIF(A2,B2,"d")

不过这种算法又有一个问题，在计算月的时候没有忽略年，在计算日的时候没有忽略月。因此，出现了一种新的方法，ym代表忽略年计算月，md代表忽略月计算日。

=DATEDIF(A2,B2,"y")=DATEDIF(A2,B2,"ym")=DATEDIF(A2,B2,"md")

现在要计算两个日期相差多少个月多少天，只需要将刚刚说的知识点结合起来，用&起来即可。

=DATEDIF(A2,B2,"m")&"个月"&DATEDIF(A2,B2,"md")&"天"

其实，公式并不难，不要想着一步登天，慢点再慢点，坚持一起学习，你会学到很多有用的知识。

Excel基础操作精选第-5.3-节自定义格式

在计算员工加班费时，首先要计算的就是加班时间，如果不跨天，直接相减就可以了，可一不小心要是加班到了次日凌晨，直接相减就不太对了，其实并非Excel计算错误，只是需要我们来指定一些特别的格式而已，跟着小编excel小课堂一起看看吧！

01课题

数据表格如图中所示，有上班时间和下班时间，在C2输入公式“B2-A2”计算时间差，可结果怎么就变成一个数字呢？

excel小课堂

02自定义时间格式h:mm

结果不对，绝对不是因为小编excel小课堂的公式有问题，也不是excel算的有问题，原因就出在小小的格式设置上，选取C2：C5单元格区域，Ctrl+1快捷键打开设置单元格格式对话框，数字选项卡-分类-自定义，在类型文本框中输入格式代码“h:mm”，点击确定按钮，excel就会以小时:分钟格式来显示加班时间了，别高兴太早，注意看最后一个，明明加班二十多个小时，怎么就只有4小时01分呢。

03自定义时间格式[h]:mm

再次强调，绝对不是公式和计算的问题，修改格式代码为[h]:mm，结果正确显示，那么这神奇的h:mm和[h]:mm究竟代表了什么意思呢？

04日期时间代码

接下来，小编excel小课堂就为大家科普一下这些个h、mm都起到了什么作用，一切都在此图中

05日期时间格式案例

如果加班只计算时间差，那就太开心了，不过这绝对是不可能的，实际中常常会出现一些意外的情况，譬如，计算加班时，如果未超出半个小时，则忽略不计，超过半小时未满1小时，则按半小时计，依次类推，听起来很复杂，其实只要一个公式就可以搞定，就是=FLOOR.MATH((K2-J2)*24,0.5)，是不是很厉害呢？

关注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表示需要取多少位的参数。

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

一起学习和探讨，谢谢

Excel没有直接提供减法函数公式，但可以用求和函数公式和减号做减法运算，因此，Excel减法函数公式主要是指求和函数公式。做减法计算时，通常直接用减号，这样比用求和函数公式书写方便，效果又完全一样。在做减法计算过程中，单纯的数字相减比较容易，比较麻烦的问题做日期时间差，特别是跨小时与跨超过两天的情况，这两种情况若直接用减法常常会得到错误的结果，因此还得用其它辅助方法。有时为便于计算还要把计算结果转天、小时或分钟，做这些转换需用Excel提供的日期时间函数。以下是Excel减法函数公式怎么用以及求日期时间差的具体操作方法，操作中所用版本为 Excel 2016。

一、Excel用“减号－”做减法运算

1、假如要求服装表二月销量与一月销量的差。选中用于保存运算结果的 G2 单元格，输入公式 =F2-E2，按回车，则求出两月销量之差；用鼠标按住单元格填充柄，往下拖，则所经过的单元格全用 G2 的值填充，按 Ctrl + S 保存，则所有行变为对应的月销量之差，操作过程步骤，如图1所示：

2、在计算结果中出现了负值，这是由于一月销量大于二月销量所致。

二、Excel减法函数公式怎么用

1、同样以计算服装表二月销量与一月销量的差为例。在 G2 单元格中输入公式 =SUM(F2,-E2)，如图2所示：

2、按回车，则求得与用减号一致的结果，如图3所示：

三、Excel用减法计算日期时间差

（一）计算结果只保留时分（h:mm）

1、在 C1 单元格中输入公式 =IF(B1 >A1,B1-A1,24-ABS(B1-A1))，按回车，则求得结果为小数，操作过程步骤，如图4所示：

2、选中 C1，按 Ctrl + 1 组合键，打开“设置单元格格式”窗口，选择左边“分类”下面的“自定义”，选择“时分（h:mm）”的时间格式，单击“确定”则 C1 单元格的数字变为 3:13，即 3 小时 13 分，操作过程步骤，如图5所示：

提示：A4 的日期时间为 2018/7/28 21:32，B4 的日期时间为 2018/7/31 0:35，两者之间相差 2 天 3 小时 2 分，而计算结果只有 3 小时 2 分，把 2 天丢了，这明显不正确，因此还要把天显示出来，方法看下面的“计算结果保留天和时分（d h:mm）”。

3、选中 C1，按住单元格填充柄并往下拖，则所经过单元格用 C1 的值填充，如图6所示：

4、按 Ctrl + S 组合键保存，则自动计算出剩余行的时间差，如图7所示：

（二）计算结果保留天和时分（d h:mm）

1、选中 C1:C5，按 Ctrl + 1 组合键，打开“设置单元格格式”窗口，选择左边的“自定义”，把 h:mm 改为 d h:mm，单元“确定”，则计算结果变为 天和时分（d h:mm）；操作过程步骤，如图8所示：

2、如果日期相差超过一个月，则日期格式需要再增加月（m)；如果日期相差超过一年，则日期格式需要再增加年（yyyy)。

（三）把计算结果转为小时

1、为便于计算，有时需要把两日期差转为小时。在 D1 中输入公式 =SUM(DAY(C1)*24,HOUR(C1),MINUTE(C1)/60)，如图9所示：

2、按回车，则求出结果 3.216666667 小时；按住单元格填充柄并往下拖，然后按 Ctrl + S 保存，则把剩余行的日期差转为小时，如图10所示：

3、公式说明：公式先从 C1 取出天，然后乘以 24 小时，则把天转为小时；然后取出小时；再取出分钟并除以60，以把分钟转为小时；最后把它们加起来。

四、Excel把表格中的所有数减去一个数

1、假如要把服装表中的所有数字减 6。选中 G2 单元格，右键 G2，在弹出的菜单中选择“复制”；框选 E2:F8，右键选中的区域，在弹出的菜单中选择“选择性粘贴”，打开该窗口，选择“运算”下的“减”，单击“确定”，则框选的所有数字都减了 6；操作过程步骤，如图11所示：

2、无论是乘除还是加减，如果要求把表格中的所有数与某一个数做运算，都可以用选择性粘贴的方法。

有些朋友被Excel中的时间差计算问题所困扰，所以今天整理了一下各种时间差、日期差的计算方法以及注意事项。

第一、计算“几小时几分钟几秒”的时间差

最简单的方法，就是用较大的时间减去减小的时间。所谓时间较大者指的是一天中更靠后的时间。

公式：=B2-B1或=TEXT(B2-B1,"h:mm:ss")

注意：原始的时间和计算结果单元格都必须设置成时间格式，如“h:mm:ss”。

如果你没有将结果单元格设置成时间格式，会得到一个数字（如0.0957）；而如果你用较小的时间减去较大的时间，那就会得到一堆的“#”。

这么说，在计算时间之前，要用眼睛判断哪个大哪个小，然后再计算咯？

当然不是，我们可以把公式变成下面这两种样子：

公式1：=B2-B1+IF(B2B2,TEXT(B1-B2,"-h:mm:ss"),TEXT(B2-B1,"+h:mm:ss"))

咦，这两个公式的计算结果有时候不一样呢？

但这两个公式都是正确的。当B2的时间数值上比B1小时，如果你用第二个公式，则表示这两个时间属于同一天，如果你用第一个公式，则表示B2的时间是第二天的时间，两者的计算结果相差24小时。

第二、计算小时差、分钟差和秒数差

在计算考勤时间时，我们不想得到“几天几小时几分钟几秒”的时间差，希望将时间差转换成小时、分钟或秒。这就可以使用上图的公式。

注意，原始的时间还是要设置成时间格式，时间差单元格设置成数值格式。用这种方法计算，会默认两个时间属于同一天。

公式中的1440表示“24小时*60分钟”，86400表示“24小时*60分钟*60秒”。

计算日期差

计算日期差，可以使用函数“DATEDIF(开始日期，结束日期、"Y/M/D")”,“Y”表示计算相差几年、“M”表示计算相差几月，“D”表示计算相差几天。

再次提醒一下，在计算时间差、日期差之前，一定要确保单元格的格式设置正确了。否则，将得到不正确的计算结果。

学习，为了更好的生活。欢迎点赞、评论、关注和点击头像。