How to set the date expiration reminder in excel

<> as shown in the figure, enter the formula in cell B2: =if(datedif(A2,Today(),"d")>0,"And there is"&datedif(a2,today(),"d")&"days expire","Expired")。Formula Analysis:

datedif(a2,today(),"d"This formula is used to calculate how many days the date column is separated from today, the datedif function, the first parameter is the start date, the second parameter is the end date, and the third parameter is whether the result of the calculation returns the number of days, years, or months. If the if function is used to judge, if it is greater than 0, it is not expiration, then it returns "how many days are left to expire", and the expired one shows "expired", which realizes automatic judgment.

Note that the today function is a volatile function, that is, open the file every day to save it, the value will be updated, and the return value of the today function depends on the system date of the computer, if the system date is set incorrectly, then the value of the today function is also incorrect.

Although we have recorded important dates, but sometimes we will forget, what to do, to set an expiration reminder, first enter the formula prompted on the screen in cell d4, and then pull down to copy the formula to prompt a few days before the important date. In the formula, & is the connector, and today() is the current date.

The original ** data is not changed, and the print date is automatically added when printing.

Let's share how to set a date expiration reminder in Excel**.

1. First of all, in the open **, check the cell that needs to set the date expiration reminder.

2. At this time, click "Start - Conditional Formatting" above.

3. In Conditional Formatting, click "Highlight Cell Rule - Date of Occurrence".

4. On the pop-up page, click the time option "Yesterday" and select "Next Month".

5. After setting the reminder time, click "OK".

6. Return to the date interface, and you can see that the expiration date of the next month has been highlighted.

1. Open the ** you need to set a reminder for.

2. Enter the formula "=if((month($b 2)=month(today()))."Yes",""so that this month's birthday will be marked as "yes".

3. This is because the "month (the cell where the date is located)" function can get the month of the specified date, and the today() function can get today's date. As long as today's month is the same as the month in which the date in ** is located, it means that the current month will expire.

4. Therefore, enter the formula "=if(and(month($b 2)=month(today())day($b 2)=day(today())."Yes",""Getting "yes" means today's birthday.

5. "month" can get the month, and "day" can get the number of days on the specified date. As long as the month and date are the same as today's, it means today's birthday.

Enter the formula: =if(a2-today()<0,"Expired","Not expired"), press Enter to finishDetailed steps:

1. Click cell B2 and enter the formula: if(a2-today()<0,"Expired"Not expired), press the Enter key to end, as shown in the following figure:

2. Select the region B2:B4, click "Start" "Conditional Formatting" and "New Rule" on the toolbar, as shown in the following figure

3. Select the last rule type, enter the formula: a2-today()<0, click "Format" and "Fill", then select red, and finally click "OK", as shown in the following figure:

4. The expiration ones are color-coded, as shown in the figure below

1.Make sure column j is in date format.

2.Select the entire column.

3.Menus, Formatting, Conditional Formatting.

4.In the dialog box, select "Formula" and enter it on the right.

Condition 1: =(today()-j1<=10)*(j1<=today()).

Condition 2: =(today()-j1<=20)*(j1<=today()).

Condition 3: =(today()-j1<=30)*(j1<=today()).

You can set different colors separately.

Method 1: Set conditional formatting.

Method 2: Enter =if(e2-today()<=0,. in cell F2"",if(e2-today()<2,"There is still an expiration date left"&text(e2-today(),"@")&"days",""It means that "1 day is left until the expiration date" is displayed if there is 1 day left until the expiration date, otherwise it will not be displayed.

Select the range of cells you need to set, set with conditional formatting, and the formula that the value of the cell is equal to is:

today()-1

OK.

<>2. Enter the entry time of the new employee in cell A2, for example: 2008 5 8 format.

3. After knowing the exact time of entry, we use the input formula in the cell B2 to quickly calculate the expiration time of the one-month probationary period.

4. Fill in the calculated expiration time downward, so that you don't have to enter them one by one, and you can also avoid date input errors.

5. Enter the formula in the cell C2 to check whether you need an expiration reminder. =and expiration time, minus today's time, less than or equal to 15, expiration time minus today's time greater than or equal to 0.

6. Fill the content of cell C2 downward, at this time we see that there are two situations, which are false and expired, and true is to remind.

7. In the d2 unit of the probationary period reminder, use if=and expiration time to subtract today's time, less than or equal to 15, the expiration time minus today's time is greater than or equal to 0, and the expiration time minus today's time has expired.

1. First of all, open Excel**, set the entry destruction time, the expiration hail time, whether you need an expiration reminder, a probationary reminder and other words that need to be sourced.

2. Then write down the detailed entry time in the corresponding ** of the entry time.

3. Then set the expiration time in the corresponding ** of the expiration time.

4, and then in whether you need an expiration reminder, enter false and expired, and true is to be reminded.

5. Finally, you can see a prompt in the reminder of the trial period.