Office hacks: Use Excel to remind special dates more intelligent

In the company culture, in order to enhance the cohesion of employees, many companies provide activities such as birthday benefits and anniversary of joining the company for their employees. However, these activities need to be implemented on specified days for specific employees, and now we can quickly add reminders for these special dates with the help of Excel. The following Excel 2016 as an example, the reminder of the time is two days before the anniversary of a certain date of entry, in the reminder cell to automatically add a reminder text, and the cell is filled with a yellow background color (Figure 1).

201924zntx1

Figure 1 Reminder effect example

You can see that the sample data is very much, and the actual reminder as long as part of the data can be, so we can first create a special worksheet for intelligent reminders, the data in the new table with the help of VLOOKUP function from the original table ABCD column data extraction. Press the prompt to create a new “reminder table”, copy the header data from A1 to D1, then locate B2, enter the formula “=IF(LEN($A2)=0,” “”, VLOOKUP($A2, Sheet1!$A$1:$D$15,COLUMN( B2),0))”, then fill it to the right to column D and down to row 15 (Figure 2).

Tip: Here use the COLUMN function to return the value of the column where the VLOOKUP function to the value of cell A2 in the original worksheet A1:D15 region to find, and finally through the IF function nested (to prevent the appearance of N/A hint) to make a judgment.

201924zntx2

Figure 2 New table

So long as we start in cell A2 to fill in the employee’s work number data, the above function will automatically refer to the original form data. Later in the original data table to add other employee information, we just continue to fill in the corresponding employee’s work number here to automatically call the data (Figure 3). Of course, this is still a finder, if many employees, as long as the work number in column A can quickly find the information of the employee.

201924zntx3

Figure 3 call the data

Because it is the employee’s anniversary reminder, we need to calculate the employee’s length of service, length of service with the help of INT function to calculate, locate the cell E2 and enter the formula “= INT ((TODAY ()-D2)/365)”, fill down to automatically display the employee’s length of service (Figure 4).

201924zntx4

Figure 4 Calculate the number of years of service

Tip: Here first use the TODAY function and employee entry date subtracted to get the actual number of days of entry, and then divided by 365 days to get the actual number of years of entry. Finally, use the INT function to round up (it is the rounding method to directly remove the decimal, for example, 9.1 → 9.9 years of service are taken 9), so that you can get the employee from the entry to today’s integer number of years of work. However, it should be noted that because of the existence of leap year (366 days), if you want to accurately calculate the employee’s years of service, you need to consider this factor, this article is only for the convenience of calculation, uniform to 365 days as the basis.

The example requires a reminder two days before the employee’s anniversary date, and the anniversary is based on the employee’s start month, so we also need to calculate the time difference between the employee’s start month and today’s date as of today. This time difference is calculated by DAYS function (month of entry – today’s month), locate cell F2 and enter the formula “= DAYS (TEXT (D2, “mm-dd”), TODAY ())”, pull down (Figure 5).

Tip: the formula first use the TEXT function to extract the cell date of D2 month and day format, and use it as the end date of DAYS function. Use TODAY () (indicating today’s month and day) as the start date of the DAYS function, and then subtracted to get the number of days difference between the two dates. Because in the example need to be notified 2 days in advance, so the number of days subtracted from 2 then need to be notified, assuming that today is 2019/11/14, then Zhang Mei, who joined the company on 2016/11/16 should get a reminder (11/16-11/14 = 2), the negative number means smaller than the current month, has passed the anniversary of the number of days.

201924zntx5

Figure 5 calculate the number of days until the anniversary of today’s entry

Finally, the setting of the reminder statement, the statement can be set with the help of IF function, positioning to cell G2 and enter the formula “= IF (F2 = 2, C2 & “of” & B2 & “the day after the entry” & E2 & “anniversary”, “”)” and then pull down, so that employees who meet the conditions in column G will appear to remind the statement (Figure 6).

Tip: IF function of the first condition to determine whether the time difference is 2 days from the entry time, if it meets then according to the example output reminder statement, the combination of statements using the “&” character will specify the characters displayed in cell G2, otherwise the display is empty.

201924zntx6

Figure 6 to add a reminder statement

In the example also need to fill the reminder statement for the background color, here with the help of conditional formatting to complete, select the G data, click “Start → Conditional Format → highlight the cell rules → for the following text cell formatting”, in the window that opens contains the text input “anniversary “, click on custom formatting, select the fill yellow background color settings (Figure 7).

201924zntx7

Figure 7 Conditional formatting settings

After completing the above settings, we just need to open this document every day, if there are eligible employees, then in column G will automatically appear to fill the yellow background of the reminder statement, according to this prompt to arrange the corresponding activities can be. In order to facilitate the writing, here you can also retain only G reminders and in cell G2 directly using IF nested E, F function, as long as the function is changed to “= IF (DAYS (TEXT (D15, “mm-dd”)), TODAY ())=2, C15 & “of” & B15 & “the day after the entry” & INT ((TODAY ()) -D15)/365)&”anniversary”,””)” can be (Figure 8).

201924zntx8

Figure 8 Final result

Because the TODAY date is dynamic, the above reminder can achieve dynamic date calculation with the help of function and conditional format. You can make smart reminders like employee birthday, company anniversary, college entrance exam countdown, etc.

QQ图片20210420165237

Leave a Comment