构建计算有效期天数的EXCEL公式，只需几个步骤！
作者：阿硕来源：部落窝教育发布时间：2021-04-12 11:28:28点击：16283
编按：
日常工作中，我们必然会遇到各种日期问题，需要对它进行查找、计算……今天，小E给大家介绍的就是计算有效期天数的方法！从简单到较难的三个不同问题，全面为大家解构公式的组成和应用！
在EXCEL中，日期的本质是数值，所以一般情况下，在计算有效期的时候，只要适当地运用日期函数，基本上都能够得出预期的结果。可是，当遇到某些奇葩的有效期计算要求的时候，就要多杀死一些脑细胞了。
小张就职于一家宠物食品公司，对宠物食品的保质期有严格的要求。小张的日常工作之一就是对保质期进行计算和管理。
最简单的日期计算
原本呢，公司生产的宠物食品的保质期，规定为70天。所以，要计算70天有效期的话，只需将生产日期加上70就可以了，这对于小张来说，是个小case。
操作：在B2中输入“=A2+70”，用鼠标向下复制填充即可。
不难的日期计算
没过多久，公司出了新的规定，规定保质期为2个月零10天。这种情况下的有效期计算也在小张的射程范围之内——先用EDATE函数求得两个月之后的日期，然后再加10，就行了。
操作：在B2中输入“=EDATE(A2,2)+10”，用鼠标向下复制填充即可。
难度升级的日期计算
近日，公司又出了更奇葩的规定——将保质期定为2个月零10天的同时，要求有效期的最后一位数字与生产日期最后一位数字保持一致！这下，情况就变得复杂了。
问题分析：
这里面会涉及到每月天数、闰年和平年2月份天数不同的问题，甚至涉及到平年跨到平年、平年跨到闰年等多种状况。以2019年12月20日为例，向后推移2个月，应为2020年2月20日，2020年2月20日之后的第一个尾数为0的日期是2020年3月10日。
在公司的这种规定下，天数差就不再是一个有明确规律的数字了，最低天数差值为69天，而最高的天数差值则高达81天：
小张一筹莫展，这真的是难死“宝宝”了。
思路：
通过上面对于天数差值的分析，大家可以看出，有效期与生产日期之间的天数差值范围，在69天至81天之间。先将生产日期往后推69天至81天的天数构成一个数组，然后再在这个数组中，把第一个与生产日期天数的最后一位数字一致的天数匹配出来。用数组来解决这个问题。
操作：
在B2中的输入：=INDEX(A2+ROW($69:$82),MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0))，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
这样，就完美的计算出了对应的有效期！
函数分析：
INDEX+MATCH函数是Excel中的经典组合，先用MATCH函数匹配到符合条件的数据的位置，再通过INDEX函数返回数组（或区域）中这个位置对应的值。
公式看起来很长，其实理解起来很简单！下面，笔者就为大家一一剖析构成这个公式的步骤：
第1步：构造日期数组
在B2中输入公式如下：=A2+ROW($69:$81) ，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
通过F9键，可以查看B2中的计算结果，如下：
={44286;44287;44288;44289;44290;44291;44292;44293;44294;44295;44296;44297;44298}
其中显示的是日期对应的数值，不太容易看清楚效果。
补充检测小技巧：
大家可以增加一列辅助列，利用TEXT函数将数值改成日期显示。
在C2中输入公式：=TEXT(A2+ROW($69:$81),"yyyy-m-d")，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
通过F9键，可以查看C2中的计算结果，如下：
={"2021-3-31";"2021-4-1";"2021-4-2";"2021-4-3";"2021-4-4";"2021-4-5";"2021-4-6";"2021-4-7";"2021-4-8";"2021-4-9";"2021-4-10";"2021-4-11";"2021-4-12"}
【注：TEXT函数在后续的计算中，用不到它。】
可以看到，在构造出的数组中，天数尾数为1的有3个，分别为2021年3月31日、2021年4月1日、2021年4月11日，这些天数的尾数都与2021年1月21日的天数尾数一致。
第2步：构建天数数组
通过DAY函数将构造出的日期数组中的每一个日期的天数提取出来，构成一个新的数组。
B2中的公式扩展为：=DAY(A2+ROW($69:$81))，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
通过F9键，可以查看B2中的计算结果，如下：
={31;1;2;3;4;5;6;7;8;9;10;11;12}
第3步：构建天数差值数组
将天数数组中的天数与生产日期中的天数相减，以求得天数差值。
B2中的函数第二次扩展为：=DAY(A2+ROW($69:$81))-DAY(A2)，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
通过F9键，可以查看B2中的计算结果，如下：
={10;-20;-19;-18;-17;-16;-15;-14;-13;-12;-11;-10;-9}
肉眼观察即知，与2021年1月21日的日期尾数相同的天数与2021年1月21日的天数差为10（2021年3月31日），-20（2021年4月1日），-10（2021年4月11日），在天数差值数组中，第一个天数差值为10的整数倍的数据，就对应着日期数组中的第一个满足条件的日期。
第4步：构建余数数组
思路：
由于满足条件的天数差值为10，-20，20，它们的一致规律就是除以10后求余数，均为0。从而，我们可以得出这样结论：第一个为余数为0的数据，也对应着日期数组中的第一个满足条件的日期。
B2中的函数修改为：=MOD(DAY(A2+ROW($69:$81))-DAY(A2),10)，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
通过F9键，可以查看B2中的计算结果，如下：
={0;0;1;2;3;4;5;6;7;8;9;0;1}
第5步：通过MATCH函数，进行匹配
通过MATCH函数，在余数数组中，匹配第一次出现0的位置。
B2中的函数修改为：=MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0)，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
通过F9键，可以查看B2中的计算结果，为1。（注：B2单元格中显示为1900/1/1，在函数栏中通过F9键查看，显示结果为1。）
至此，在构建的日期数组中，第一个满足条件的日期出现在第1个位置，大家再用INDEX函数去日期值数组中，返回第1个日期值，就OK了。
第6步：通过INDEX+MATCH函数组合，完成日期匹配
B2中的公式得到最终的完善，得到最开始的公式：=INDEX(A2+ROW($69:$82),MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0))。最后，与上述步骤一致，按Ctrl+Shift+回车键后，用鼠标将公式向下复制填充。
这样，令小张头疼已久的有效期问题就解决了。
亲爱的小伙伴，本文中问题的涉及到数组、MOD函数、MATCH函数、INDEX函数等综合应用，请务必多次练习，从而明白其中的奥义哦！
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
本文作者阿硕；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。