Ways to implement automatic expiration reminders using Excel

ways to implement automatic expiration reminders using Excel

Here is a smart bank deposit expiration reminder function.

Original table:

Requirements:

30 days from maturity fill in red, 60 days filled with yellow.

Effect:

You can also modify the G2:g3 period, and the reminder color is updated automatically

It's fun and it's practical.

Operation Steps:

Step 1, add the Expiration days column to calculate how many days out of the repayment.

Formula =d2-today ()

Step 2, add a secondary table that you can use to dynamically adjust the number of reminders.

15 means within 15 days, 45 means 45 days.

Step 3, select the table (header row not selected), start-Conditional formatting-create a new rule. Then make the following settings:

Type: Use formula to determine ....

Set the formula in the Format box = $E 2< $G $ (to be judged by the expiration date of E, add $ before E)

The DOT Format button to set the fill color to red.

Step 4, follow the steps 3 method, and add a rule. The formula is = $E 2< $G $, and is formatted with yellow.

Step 5, start-conditional format-manage rules, and in the Rule Management window, elevate the 30-day rule to the top.

Setup Complete!

Add: Conditional format of the formula set must pay attention to, accidentally error. Reference cell addresses and references in formulas students should see clearly.