Excel时间计算教程：如何自动计算结束日期

编按：在EXCEL日常工作中，如果需求中带有“自动”字样，往往都会用到VBA，今天就来给大家讲一讲，如果不会VBA那该如何处理呢？比如下面这个通过起始时间、周期，来计算结束日期，以及还有多少天结束的情况！

最近看到一份有意思的需求，拿出来给大家分享一下。

需求如下：

1、在A列、B列填入内容后，C列自动计算，但是“日”要是A列“日”的前一天。例如：A2=2021-08-06，B2=10，那么C2=2022-06-05；

2、根据C列内容，自动计算从今天算起还有几天到结束日期，今天也算1天。例如：C2=2022-06-05，那么D2=52；

3、D列字体需要自动有“变色功能”，大于30天为【黑色】，小于等于30天为【红色】；

4、按照D列的天数，把A、B、C三列自动填充背景色，7天以内（含7天）为【红色】，8至15天（含15天）为【黄色】，16至30天（含30天）为【蓝色】，31至60天（含60天）为【绿色】，60天以上不标记颜色；

5、自动按照D列的天数升序排列。

初看需求以为是个“青铜”，细一看才发现原来是个“王者”。

基本每一个需求都有【自动】两个字，那我们先给大家展示一下，如何在不使用VBA的情况下实现“自动”？

C列“预埋”函数，因为不知道要输入多少行内容，10行？100行？….10000行？好吧，预埋到20000行也就是了。

首先，在名称框中输入单元格区域C2:C20000，然后按回车键；

此时就选中了C2:C20000单元格区域，然后输入函数

=IF(AND(A2<>"",B2<>""),EDATE(A2,B2)-1,"")

按CTRL+ENTER组合键结束录入，此时C2:C20000就有了“预埋”函数。

在D2单元格输入函数=IF(C2="","",DATEDIF(TODAY(),C2,"d"))

注意一点：需求中没有说结束日期小于当前日期的情况，所以我们可以给个提示询问一下，或者直接用IFERROR函数容错也可以。

然后选中D2单元格，使用“条件格式”设置字体颜色，如下图所示：

再双击D2单元格的填充柄，填充至D20000单元格。

选中A2:C2单元格区域，根据需求使用“条件格式”依次新增4个条件格式规则，为每个规则设置不同的填充背景色。注意公式中需要锁定列，如下图所示：

然后我们再把“条件格式”中的【应用于】参数的单元格区域改成【=$A$2:$C$20000】。

及此，“可以自动的需求部分”就给大家分享完了，我们来看看效果吧！

至于最后一个需求：自动排序，常规的操作下，排序是没有“自动”功能的。

今天我们分享个简单的VBA代码，可以实现一键排序，如下：

Sub 排序()

With Sheets(1)

Set rg = .Range("A1:D" & .[B65000].End(3).Row)

rg.Sort key1:="还有几天结束", order1:=xlAscending, Header:=xlYes

End With

End Sub

按ALT+F11，打开VBE界面，在左侧工程栏，鼠标右键——插入——模块，然后在代码区，输入上面的代码。

回到工作表界面，在工具栏——“开发工具”——插入——表单控件——按钮控件，插入一个按钮控件，按下图设置内容。

及此，此需求就全部完成了，我们看看排序的效果吧！

特别提醒：使用VBA后，一定要把文件另存为.XLSM格式的文件哟！

好啦，以上就是今天的所有内容，感谢你的观看！

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

学习交流请加微信：hclhclsc进微信学习群。

如何提取唯一值？试试TEXTJOIN函数搭配VBA自定义！

别怕，VBA入门级教程来了，条件语句很简单！

如何在特定位置批量插入空行等12种实用办公技巧

工资表转工资条，VLOOKUP有绝招！

版权申明：

本文作者E图表述；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。