别再百度了！Excel里90%的日期问题一文解答！

我们绝大多时候，都会接触到各种办公中常见的日期问题，比如：

① 为什么输入日期 1.10 就变成 1.1？

②日期和时间如何快速合并 or 拆分？

③如何计算员工转正时间、工龄年月？

④带调休的工作日天数，又怎么解决？

看了一脸懵？没关系

今天小可教大家，12个使用频率最高的「日期时间计算的技巧」，工作效率噌噌上涨！

1

输入 1.10和2020.05.06格式

最近有小伙伴问，输入日期 1.1~1.9 号都是OK，但是到1.10 就会自动变成 1.1，如何批量修改？

如下GIF：把单元格设置为文本格式 - 再敲日期，就ok啦。

同理，如下GIF，即可快速换成 2020.05.06 的格式！

步骤：

选择单元格 - 按【Ctrl+1】打开弹框 - 设置自定义单元格格式为：yyyy.mm.dd  - 【确定】即可。

讲解👇：

由于1 和 10 在 Excel 里默认是一个数字。

一个小数，小数点后面的零，默认都会被舍去，因为没啥意义。

2

合并日期和时间

如下GIF，如何将A列日期和B列的时间合并到一起，变成既有日期又带有时间的数据呢？

步骤：输入公式 =A2+B2，把日期和时间相加，批量填充即可。

哈哈哈，超简单！

3

拆分日期和时间

如果要将日期和时间合并在一起的数据拆分开，要怎么处理呢？别着急！

如下GIF：

① 公式：=INT(A2)，得到日期。

②公式：=A2-B2，用日期时间数据减去日期即可，得出时间。

4

提取月份

当小伙伴要根据员工生日的月份，去找出5、6 月的员工，给他们送生日礼物，咋搞？

直接去看出生日期嘛？太费眼了！提取出月份就好办啦！

如下GIF：输入公式 =MONTH(B2)。

一个MONTH 函数搞定！

此外，还有 YEAR 提取年份，DAY 提取天数。

5

计算上班是否迟到

假如老板给你一份考勤记录，有考勤时间，叫你判断员工是否迟到，你有想到哪些方法？

如下GIF：

输入公式：=IF((E3-INT(E3)-'08:00')>0,'迟到','')，批量填充。

讲解👇：

①首先，日期时间本质是数字，整数部分是天数，小数部分是时间。

②要算迟到得把时间提取出来，也就是把小数弄出来。

=E3-INT(E3)。 作用：INT 函数专门提取整数，再用原来的数字减去整数，就是小数了。

③=E3-INT(E3) -'8:00'。用这个小数直接减去上班时间 08:00，结果>0 就是迟到。

注：时间不能直接减，用双引号写成时间格式，才能计算正确。

④ 最后外套一个 IF 函数，把红色<0 的数字，变成空白，黑色>0 的数字变成「迟到」啦。

6

计算间隔几小时/分钟

如何计算两个时间之间有多少个小时或分钟呢？

如下GIF：

①间隔小时：公式 =(B2-B1)*24。

讲解👉：用结束时间减去开始时间，然后乘以24就ok。

②间隔分钟：公式 =(B2-B1)*1440。

7

突出显示周末日期

如下GIF：

步骤：

①选中A3:F7单元格区域，新建格式规则。

②输入公式：=WEEKDAY(A$3,2)>5。

③设置突出显示的格式。

讲解👇：

①WEEKDAY函数返回某日期为星期几。第二参数使用2，表示以1~7表示星期一到星期日。

②对第三行中的日期进行判断后，如果数值大于5，即表示该日期为周六或是周日。

8

指定日期所在月有几天

如下GIF，公式：=DAY(EOMONTH(A2,0))。

讲解👇：

EOMONTH函数返回在某个日期之后指定月份后的最后一天。

EOMONTH(A2,0)函数得到日期所在月最后一天的日期，DAY函数返回该日期是几号。

9

根据身份证号计算出生年月

如下GIF，公式：=1*TEXT(MID(B2,7,8),'0-00-00')

讲解👇：

①先使用MID函数从B2单元格的第7位开始，提取出表示出生年月的8个字符，结果为：'19780215'。

②再使用TEXT函数将字符串转换为日期样式：'1978-02-15'

③然后通过*1计算，将其转换为真正的日期。最后设置为日期格式即可。

10

计算转正时间

假如要根据B列的员工入职日期和C列的试用期月数，计算员工转正日期。

如下GIF：公式：=EDATE(B2,C2)

讲解👇：

EDATE函数用于指定某个日期N月之后或是N月之前的日期。

用法为：=EDATE(开始日期,指定月份)

11

计算工龄年月

如下GIF，要根据员工入职日期，按月计算工龄：

①求工龄（月）公式： =DATEDIF(B2,'2017-5-30','m')

②求工龄（年）公式： =DATEDIF(B2,'2017-5-30','y')

讲解👇：

DATEDIF函数用于计算两个日期之间的间隔，第二参数使用m，表示计算间隔的整月数。

如果第二参数使用y，则表示计算间隔的整年数。

12

带调休的工作日天数

实际工作中，要计算工作日时，除了要考虑去除周末和法定节假日的工作日天数，还要加上调休日。

对于这种情况，咱们需要先建立一个调休和法定节假日的对照表。

如GIF，F列是放假时间，G列是调休上班时间。

步骤：

在C2单元格，输入公式就ok：

=NETWORKDAYS(A2,B2,F$2:F$28)+COUNTIFS(G$2:G$28,'>='&A2,G$2:G$28,'<='&B2)

讲解👇：

①先使用NETWORKDAYS函数计算出去除法定节假日后的工作日天数。

②然后用COUNTIFS函数计算从开始日期到结束日期这个阶段中，有几天是需要调休上班的。

③把这部分调休的天数加上，就是最终的工作日天数了。

好啦~今天就分享到这儿！赶紧get起来！