How to set reminders in Excel
By
SpreadCheaters
Setting reminders in Excel enhances productivity, ensures timely actions, and helps you stay organized. It can be particularly beneficial for managing deadlines, time-sensitive tasks, events, and data analysis ultimately improving efficiency and reducing the risk of overlooking important actions or events.
Consider a dataset with three columns: Names, Birthdays, and Reminders. This dataset will serve as the basis for learning how to set reminders in Excel. By utilizing this dataset, we can explore the process of setting reminders within Excel and understand how it can be beneficial for managing birthdays, events, or other time-sensitive information.
Method 1 – By using “IFS” and “TODAY” Functions
Understanding Functions and their syntax
IFS Function:
The IFS function in Excel is a logical function that allows you to evaluate multiple conditions and return a corresponding value based on the true condition. It simplifies nested IF statements and makes it easier to handle complex logical evaluations. The syntax for the IFS function is as follows:
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …, logical_testN, value_if_trueN, [value_if_false])
logical_test1, logical_test2, …, logical_testN: These are the conditions or logical expressions that you want to evaluate. Each logical_test is separated by a comma.
value_if_true1, value_if_true2, …, value_if_trueN: These are the values or results to be returned if the corresponding logical_test is true.
[value_if_false]: (Optional) This is the value or result to be returned if none of the logical tests are true. If not provided, the function returns a #N/A error.
The IFS function checks each logical_test in order, and when it finds the first true condition, it returns the corresponding value_if_true. If none of the conditions are true, it returns the value_if_false (if provided) or the #N/A error.
TODAY Function:
The TODAY function in Excel is a built-in function that returns the current date. It does not require any arguments or parameters, making its syntax simple. Here’s the syntax for the TODAY function:
=TODAY()
The function is written as “=TODAY()” and is placed in a cell where you want the current date to be displayed. When you enter this formula in a cell and press Enter, the cell will display the current date based on your computer’s system date. The date will automatically update each time the worksheet is recalculated or when the file is opened on a new day.
Step 1 – Select the cell
- Select the cell in which you wish to set the reminder.
- In this cell, we will apply “IFS” and “TODAY” Functions to set birthday reminders.
Step 2 – Write the formula and implement it
- We will write the following formula in the cell.
=IFS(B2=TODAY(),”Birthday”,B2<>TODAY(),”Not Birthday”)
IFS function: The IFS function is a logical function in Excel that allows you to test multiple conditions and return a value based on the first condition that evaluates to TRUE. It takes pairs of conditions and corresponding values as arguments.
B2=TODAY(): This is the first condition being tested within the IFS function. It checks if the date in cell B2 is equal to the current date returned by the TODAY function. If the condition evaluates to TRUE, the corresponding value “Birthday” will be returned.
“Birthday”: This is the value associated with the first condition. If the date in B2 matches the current date, the value “Birthday” will be returned by the IFS function.
B2<>TODAY(): This is the second condition being tested within the IFS function. It checks if the date in cell B2 is not equal to the current date. If the condition evaluates to TRUE, the corresponding value “Not Birthday” will be returned.
“Not Birthday”: This is the value associated with the second condition. If the date in B2 does not match the current date, the value “Not Birthday” will be returned by the IFS function.
- Press Enter button after writing the formula and the result would appear as “Birthday” if it is the birthday of the person in adjacent cells and “Not Birthday” if it is not the birthday of the person in the adjacent cells.
Step 3 – Implement the formula to the whole range
- To apply the formula across a range of cells, begin by selecting the cell containing the desired result, such as cell C2.
- Next, navigate to the bottom right corner of the cell until your cursor transforms into a black colored plus (+) shape, known as the fill handle.
- Double-Click on this fill handle and the formula would be applied to the whole range.
Method 2 – By using a VBA code
One advantage of utilizing VBA code to establish reminders is the ability to execute the macro, enabling us to receive a prompt through a message box indicating whether it is someone’s birthday or not. This approach offers convenience and efficiency by automating the reminder process, eliminating the need for manual tracking.
Step 1 – Add a module
- For adding a module, navigate to the Developer tab.
- After that, click on the first option named Visual Basic.
- You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
- Then, click on the Insert tab in this window and click on the Module option.
- Now, a new module would open.
Step 2 – Write the code
- Write the following code after adding a module (You would have to change the code according to your preference of reminders).
Sub BirthdayReminder()
Dim namesRange As Range
Dim datesRange As Range
Dim nameCell As Range
Dim dateCell As Range
‘ Set the range for names and dates
Set namesRange = Range(“A2:A11”)
Set datesRange = Range(“B2:B11”)
‘ Loop through the names and dates
For Each nameCell In namesRange
‘ Check if the corresponding date cell is today’s date
Set dateCell = datesRange.Cells(nameCell.Row – namesRange.Row + 1)
If DateValue(dateCell.Value) = Date Then
‘ Display a message box with the birthday message
MsgBox “Happy birthday, ” & nameCell.Value & “!”
Exit Sub ‘ Exit the loop after finding the first birthday
End If
Next nameCell
‘ If no birthdays are found, display a message box
MsgBox “No birthdays today.”
End Sub
- Then close the VBA Editor.
Step 3 – Run the macro
- Navigate to the “Developer” tab on the Ribbon. If you don’t see the “Developer” tab, you may need to enable it first. To do so, go to the “File” menu, select “Options,” choose “Customize Ribbon,” and check the “Developer” option.
- Within the “Developer” tab, locate the “Macros” button and click on it. This will open the “Macro” dialog box.
- In the “Macro” dialog box, you should see a list of available macros in the workbook or application. Look for the “BirthdayReminder” macro in the list and select it.
- Click the “Run” button to execute the selected macro.
- The “BirthdayReminder” macro will now be triggered, and depending on its functionality, it may display a message box and tell whose birthday is it or tell you that there are no birthdays today.
Explanation of the formula used in Method 1:
The formula “=IFS(B2=TODAY(), “Birthday”,B2<>TODAY(),”Not Birthday”)” is an Excel formula that uses the “IFS” function to determine whether a date in cell B2 matches today’s date. If B2 is equal to today’s date, it will display “Birthday”; otherwise, if B2 is not equal to today’s date, it will display “Not Birthday”. This formula allows for conditional logic to dynamically evaluate the date in B2 and provide the appropriate output based on its relationship to the current date.
Explanation of the code used in Method 2:
The provided VBA code defines a subroutine named “BirthdayReminder”. It begins by declaring and initializing variables for storing ranges of names and dates. The code then loops through each name and checks if the corresponding date is today’s date. If a match is found, a message box is displayed with a birthday message, and the loop is exited. If no birthdays are found, another message box is displayed indicating that there are no birthdays today. This code enables automated birthday reminders based on the current date, making it easier to remember and acknowledge important occasions.
