This tutorial will demonstrate how to set the due date reminder formula in Excel. In Microsoft Excel, we can use different methods to set the due date reminder. When you are running a company or supervising a project, it is very obvious, you have to take outputs or take money from customers. And for that reason, you need to set a due date reminder. This reminder not only helps to set the overall structure of your overall cash flow but also helps you to understand the behavior of your clients-like who is good with the money and who has the habit to keep the unnecessary due. By that, you can sort the clients accordingly to have a better client section for your company. So, it is very important to learn how to set the due date reminder formula in Excel.
Set Due Date Reminder Formula in Excel: 2 Easy Examples
We’ll use a sample dataset overview as an example in Excel to understand easily. For instance, we have Buyer in column B, Amount in column C, Due Date in column D, and Output in column E. If you follow the steps correctly, you should learn how to set the due date reminder formula in Excel on your own. The examples are
1. Using Combined Functions
In this case, our goal is to learn how to set the Excel due date reminder formula with a combination of different functions. In this case, we have TODAY, AND, and IF functions to use for fulfilling our goal. The TODAY function returns the current date formatted as a date. The AND function checks whether all the arguments are TRUE, and returns TRUE if all arguments are TRUE. The IF function checks whether a condition is met and returns only the TRUE value, and sometimes also returns the FALSE value. After that, you can easily use these formulas to set the due date reminder within the proper condition. The steps of this method are as follows.
Steps:
- First, insert the following formula in cell E5.
=IF(AND(D5<>"",TODAY()+$D$11>=D5),"Yes","No")
⏬ Formula Breakdown:
➤ TODAY()+$D$11>=D5
It will check the date- 7 days ahead of the current date whether it is greater than or equal to the date of Cell D5 or not. It will return as-
Output: TRUE
➤ D5<>””
It will check if the Cell D5 is empty or not and will return as-
Output: TRUE
➤ AND(D5<>””,TODAY()+$D$11>=D5)
Then the AND function will combine both outputs. If any output goes FALSE, it will return FALSE and if both are TRUE, then it will return TRUE–
Output: TRUE
➤ IF(AND(D5<>””,TODAY()+$D$11>=D5), ”Yes”,” No”)
Finally, the IF function will return Yes for TRUE and No for FALSE.
Output: Yes
- In addition, after pressing the Enter button, you will get the result for this cell. Moreover, use the Fill Handle to use the formula for all the cells.
- Finally, you will get the final result. In this case, you will see that the Output boxes of Pam, John, and Ron have Yes. This means they should be reminded about the due dates. On the other hand, Michael and Newman have No outputs which mean they don’t need to be reminded about any due dates.
Thus, we have set the due date reminder with the help of combined functions in Excel.
Read More: How to Create Notifications or Reminders in Excel
2. Use of Conditional Formatting
Now, our goal is to learn how to set the Excel due date reminder formula with conditional formatting. Conditional formatting is now an integral part of the Microsoft Excel experience which helps us to identify particular values in a random or large group of data. We can not only identify certain values but also can identify which belong to a certain range or not in it, string values, numeric values, errors, etc. with conditional formatting. It is, as the name suggests, Excel formatting a cell based on whether a condition is true or not. We will use this conditional formatting to fulfilling our goal. First, take a dataset and after that, you can easily choose a due date to use it as a parameter to set the reminder with help of it. The steps of this method are as follows.
Steps:
- First, arrange the dataset like the first image.
- Next, select the cells>>go to Home>> Conditional Formatting>> New Rule options.
- Then, in the New Formatting Rule window, you have to select Format only Cells that contain in the Select a Rule Type option.
- Afterward, in the Format Only Cells with option select the less than or equal to option and insert the rule you want to use, and press the Format option.
- Now, in the Format Cells window box, go to the Fill option and select the color you want to use, and press OK.
- Moreover, press OK to use the set conditions on your worksheet.
- Lastly, you will get the final result accordingly with the help of Excel Conditional Formatting. In this case, you will see that the Due Dates of Pam, John, and Ron have been marked with the formatting color. This means they should be reminded about the due dates. On the other hand, Michael and Newman have non-marked boxes which means they don’t need to be reminded about any due dates.
Hence, we have set the due date reminder with the help of the conditional formatting feature in Excel.
How to Set Due Date Reminder Using Excel VBA
Next, our goal is to learn how to set the Excel due date reminder formula with help of VBA code. We can learn the method by following the steps below.
Steps:
- First, arrange the dataset like the first image.
- After that, go to the Developer Tab and then select Visual Basic.
- Next, the VBA editor will appear. Select Insert >> Module to open a VBA Module.
- Now, type the following code in the VBA window.
Option Explicit
Sub Remind_DueDate()
Dim DueDate1x_Col As Range
Dim DueMy As Range
Dim Pop_Noti1 As String
Set DueDate1x_Col = Range("D5:D9")
For Each DueMy In DueDate1x_Col
If DueMy <> "" And Date >= DueMy - Range("D11") Then
Pop_Noti1 = Pop_Noti1 & " " & DueMy.Offset(0, -2)
End If
Next DueMy
If Pop_Noti1 = "" Then
MsgBox "No need to Run Today."
Else: MsgBox "The Buyers Need to Run Today: " & Pop_Noti1
End If
End Sub
- Fourth, press the F5 or Run option to get the final result using the VBA Now, you will get a small dialog box in which you will see the buyer’s name which should run to remind clients of their due dates (in this case, we have Pam, John, and Ron). It also means others (in this case Michael and Newman) don’t have to remind their clients about the due dates.
Therefore, we have determined to set the due date reminder with the help of the Excel VBA code.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods, and hopefully, these methods will help you set the Excel due date reminder formula. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.
Related Articles
- How to Create Popup Reminder in Excel
- How to Generate Automatic Email Alerts in Excel
- How to Create Alerts in Excel
- How to Disable Alerts in Excel VBA
<< Go Back to Alerts in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
