Office hacks: timed reminders do not panic Excel to create intelligent reminders

Usually there are always many things that need to be reminded in work or study, such as the day to take the entrance exam, girlfriend’s birthday, weekly meetings, etc.. We can use Excel functions to create a practical and intelligent reminder, so you do not have to worry about ignoring these important days.

Weekly meeting easily reminded

The company requires the last Friday of every month to hold a regular meeting, as the administrative receptionist Wang in this time are to notify everyone to attend the meeting.

First of all, start Excel, and then in cell A2 to A13 in order to fill in the input “2020-1-1” to “2020-12-1” date, in cell B2 enter the formula “= EOMONTH (A2,0) – MOD (WEEKDAY (EOMONTH (A2,0)) +1,7)”, after pulling down you can get the time of the last Friday of all months in 2020 (Figure 1).

Tip: the above formula to the date of cell A2 as a benchmark, use EOMONTH (A2,0) to seek the last day of the month, use WEEKDAY to return to the value of Friday and use the MOD function to take the remainder, and then use the two values subtracted to get the date of the last Friday.

202004zntx1

Figure 1 to get the last Friday of all months in 2020 time

Continue to enter the formula “= DATE (YEAR (NOW ()), MONTH (NOW ()) +1, 0) – MOD (WEEKDAY (DATE (YEAR (NOW ()), MONTH (NOW ()) +1, 0), 16), 7)” in cell C2, in In D2, enter “=TODAY()”, and in E2, enter “=IF(C2=D2, “NOTE: Meeting is called today”,””)”, which means that if the meeting time is today, then the words “NOTE Notice of meeting today”.

Tip: The above formula uses DATE nested YEAR, NOW, MONTH function to get the last day of the month, the same with WEEKDAY to return to the value of Friday and use the MOD function to take the remainder, and then use the two values to subtract the date of the last Friday. Because the NOW function is used here, it will automatically change the display value according to the current month after each open.

Finally, select cell E2, click “Start → Conditional Format → highlight the cell rules → for the cell containing the following text formatting”, in the window that opens contains the text enter “Note: Today’s notice of meeting”, set to “light red fill” (Figure 2).

202004zntx2

Figure 2 conditional formatting settings

So long as Wang opened this document every day, reminders will automatically update the time, each time the meeting time will automatically be a striking reminder (Figure 3).

In addition, the intelligent reminder here is mainly C2, D2, E2 function, so you can also paste this function in the document that must be opened every day, such as the top of the company’s employee attendance sheet and other documents, you can be reminded when viewing the attendance file, without worrying about forgetting.

202004zntx3

Figure 3 Smart Reminder

Countdown reminders for important days

To prepare for some important days, we often use a countdown timer to make reminders. For example, a company’s production line will be officially put into operation on April 1, 2020, and now it needs to display a countdown reminder on the company’s big screen. For this type of countdown prompts, with the help of Excel’s date calculation function can be completed.

First of all, enter the official start date in cell A2, then in cell L17 (optional, here to facilitate the center of the display) enter the formula “= IF (A2 = TODAY (), “today officially put into production!” , “from the official start of production there” & A2-TODAY () & “days”)”, so that you can display the countdown text here (Figure 4).

Tip: The above formula uses the contents of cell A2 and today’s date subtracted as the countdown days, and then use the “&” will correspond to the text connected. Finally, use IF to judge, if the date of production and the same day, then display “officially put into production today!” otherwise the actual countdown days are displayed.

202004zntx4

Figure 4 Data settings

In order to facilitate the display on the large screen, you can right-click column A and select “Hide”, click the menu bar in the “View → remove the check box of the grid line”, and will automatically hide the function area, set the appropriate font size and font, the output to the large screen display can be. Because of the use of the TODAY function here, so every day after opening the document will automatically calculate the number of days countdown (Figure 5).

202004zntx5

Figure 5 countdown timer display

If you need to accurately count down to the hour, minute, seconds, then the date format of cell A2 should be set to “2020/4/1 9:00:00”. The same in cell L17 enter the formula “=” from the official start of production and “& INT (A2-NOW ()) & “days” & TEXT (A2-NOW () – INT (A2-NOW ()), “h time m minutes s seconds”), so that the exact countdown text will be displayed (Figure 6).

Tip: The above formula uses the difference of “A2-NOW()” as the countdown display, and then use the INT function to round up the date after completing the subtraction, so that it will display the day, hour, minute, and second, and finally use “&” to connect the corresponding characters.

202004zntx6

Figure 6 Displaying the exact countdown time

However, the NOW function can not automatically refresh the display in Excel, in order to be able to keep displaying the countdown time, you need to use VBA script to achieve. Hold down the Alt+F11 keys to open the VB edit window and create a new script code as follows (Figure 7).

Sub aa()

ActiveSheet.Calculate

Application.OnTime Now + TimeValue(“00:00:01”), “aa”

End Sub

202004zntx7

Figure 7 Code settings

Return to the Excel window, save the file as “Excel macro-enabled workbook”. As above, cancel the grid lines and ribbon auto-hide, open the file again and click “Development Tools → Macro → Run named aa macro”, the countdown will automatically refresh the display (Figure 8).

202004zntx8

Figure 8 Precise countdown display effect

QQ图片20210420165237 

Leave a Comment