excel vba到期提醒_在Excel中监视到期日期

excel vba到期提醒

Do you use Excel to keep track of software subscriptions, or domain registrations, or other things with an expiry date? It's important to keep track of those dates, so here's an example of how to monitor expiry dates in Excel, and see what needs to be renewed soon.

您是否使用Excel来跟踪软件订阅，域注册或其他具有到期日期的内容？ 跟踪这些日期很重要，因此这里有一个示例，说明如何在Excel中监视到期日期，并查看需要尽快更新的内容。

到期日列表 (Expiry Dates List)

Here's a simple list of expiry dates for Microsoft Office subscriptions. Those have to be renewed every year, so that you don't arrive at the office one day, and find out that you can't use Excel. Oh, the horror!

这是Microsoft Office订阅的有效日期的简单列表。 这些必须每年更新一次，这样您就不会有一天不去办公室，并且发现您无法使用Excel。 哦，恐怖！

到期天数 (How Many Days to Expiry Date)

To make it easy to see which subscriptions are expiring soon, I'll use conditional formatting to highlight anything that will expire within the next 30 days.

为了便于查看哪些订阅即将到期，我将使用条件格式突出显示将在未来30天内到期的所有内容。

I could make a conditional formatting rule that is based on the Expiry Date column, but I find it easier to check regular numbers, instead of dates.

我可以制定基于“到期日期”列的条件格式设置规则，但我发现更容易检查常规数字而不是日期。

So, I'll add a new column, with a formula to calculate the number of days there are before the expiry date. In the screen shot below, you can see that new column – DaysToExpiry.

因此，我将添加一个新列，其中包含一个公式来计算到期日之前的天数。 在下面的屏幕快照中，您可以看到新列– DaysToExpiry。

The formula in that column F subtracts the current date from the Expiry Date: =D3-TODAY()

F列中的公式从到期日期中减去当前日期： = D3-TODAY()

The results look a bit strange though, when you hit the Enter key, to add that formula.

但是，当您按Enter键以添加该公式时，结果看起来有些奇怪。

修正结果 (Fix the Results)

Because the formula refers to a date cell, Excel "helps" us, by formatting the result as a date too.

由于公式引用的是日期单元格，因此Excel也会通过将结果设置为日期格式来“帮助”我们。

A couple of the rows have expiry dates in the past, and those rows show number signs in the DaysToExpiry column. Excel can't convert negative numbers to dates, so it shows those number signs instead.

有几行具有过去的过期日期，并且这些行在DaysToExpiry列中显示数字符号。 Excel无法将负数转换为日期，因此会显示这些数字符号。

To fix all the results:

要修复所有结果：

• Click in the heading of the DaysToExpiry column, to select all the table rows.
  单击DaysToExpiry列的标题，以选择所有表行。
• On the Home tab of the Excel Ribbon, choose General or Number as the Number Format.
  在Excel功能区的“主页”选项卡上，选择“常规”或“数字”作为“数字格式”。

突出显示即将到期的日期 (Highlight Upcoming Expiry Dates)

To make the upcoming expiry dates stand out, we'll use conditional formatting. Based on the result in the DaysToExpiry column, we can highlight the expiry dates which are 30 days (or less) from today.

为了使即将到来的到期日期更加突出，我们将使用条件格式。 根据DaysToExpiry列中的结果，我们可以突出显示距今天30天(或更短)的到期日期。

To add the conditional formatting:

要添加条件格式：

• • Select all the data rows in the expiry date table
    选择到期日期表中的所有数据行
  • On the Home tab of the Excel Ribbon, click Conditional Formatting, then click New Rule
    在Excel功能区的“主页”选项卡上，单击“条件格式”，然后单击“新建规则”
  • In the New Formatting Rule window, in the "Select a Rule Type" section, click "Use a formula to determine which cells to format"
    在“新格式设置规则”窗口的“选择规则类型”部分中，单击“使用公式确定要格式化的单元格”
  • • =$F3<=30
      = $ F3 <= 30
    • NOTE: The formula uses an absolute reference to column F (DaysToExpiry), and a relative reference for the row number.
      注意：该公式使用绝对引用F列(DaysToExpiry)，并使用相对引用作为行号。
  • Click the Format button, and choose a fill colour to highlight the rows with upcoming Expiry dates
    单击格式按钮，然后选择一种填充颜色以突出显示即将到期日期的行

• Click OK to close the Format window, then click OK to close the New Rule window
  单击“确定”关闭“格式”窗口，然后单击“确定”关闭“新规则”窗口

The table rows with upcoming expiry dates are highlighted with the colour that you selected.

具有即将到期日期的表行将以您选择的颜色突出显示。

更多到期日警告 (More Expiry Date Warnings)

With conditional formatting on the expiry date list, you'll quickly see which subscriptions need to be renewed soon. Just open the workbook every day, and renew those subscriptions, as soon as they're highlighted. Then, remember to change the Expiry Date, so you'll get a warning next year too.

使用到期日期列表上的条件格式设置，您将很快看到需要尽快续订的订阅。 只要每天打开工作簿，并在突出显示这些订阅后就对其进行续订即可。 然后，请记住更改到期日期，以便明年也将收到警告。

If you want additional warnings, my sample file also shows how to make a hyperlink appear, in a SendEmail column. You could click that to send an email about the expiring subscription, if someone else is supposed to renew it. Or, have a link to the website where you go to renew the subscription.

如果您需要其他警告，我的示例文件还将在SendEmail列中显示如何显示超链接。 您可以单击该按钮以发送有关即将到期的订阅的电子邮件(如果有人应该续订)。 或者，具有指向您要续订订阅的网站的链接。

到期日摘要 (Expiry Dates Summary)

My sample file also has a Summary sheet -- it shows the total count of expiry dates, and the number that are expiring soon.

我的样本文件还具有一个摘要表-它显示了到期日期的总数以及即将到期的数字。

If you don't want to open the Expiry Dates workbook every day, you could link those summary cells to another workbook – one that you DO open every day.

如果您不想每天打开“到期日期”工作簿，则可以将这些摘要单元格链接到另一工作簿，即您每天都要打开的工作簿。

下载样本文件 (Download the Sample File)

Get the sample Excel file for this tutorial from my Contextures website. Go to the Excel Sample Files page, and in the Conditional Formatting section, look for CF0008 - Expiry Date Warning. The zipped file is in xlsx format, and does not contain any macros.

从我的Contextures网站获取本教程的示例Excel文件。 转到“ Excel示例文件”页面 ，然后在“条件格式”部分中，查找CF0008-到期日期警告 。 压缩文件为xlsx格式，不包含任何宏。

And while you're on the Sample Files page, take a look around. There are lots of other files there – you might find some other interesting techniques to try.

当您在“示例文件”页面上时，请四处看看。 那里还有许多其他文件–您可能会找到其他有趣的技术来尝试。

翻译自: https://contexturesblog.com/archives/2018/05/10/monitor-expiry-dates-in-excel/

excel vba到期提醒