After fixing a due date in Excel for any issue, you may want to check if any other date has passed the due date or not and show it with a reminder. This article will show 3 quick and effective methods to set due date reminder in Excel.
Set Due Date Reminder in Excel: 3 Ways
To demonstrate the methods we’ll use the following dataset that represents some buyers’ invoice amounts and due dates.
1. Combine TODAY, AND, and IF Functions to Set Due Date Reminder in Excel
In our very first method, we’ll make a formula to get the due date reminder. For that, we’ll use the TODAY, AND, and IF functions. We’ll check for the date 7 days ahead of the current date. The formula will return Yes if any date passes the due date; otherwise, it will show No. To show the output we added a new column E.
- Activate Cell E5.
- Type the following formula in it-
- Then just press the Enter button.
After that, drag down the Fill Handle icon to copy the formula for the other cells.
Soon after, you will get all the outputs like the image below.
⏬ Formula Breakdown:
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-
It will check if the Cell D5 is empty or not and will return as-
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–
Finally, the IF function will return Yes for TRUE and No for FALSE.
2. Apply Conditional Formatting to Set Due Date Reminder in Excel
Here, we’ll learn to apply Conditional Formatting to set a due date reminder by highlighting cells with picked color.
- Select the dates.
- Later, click as follows: Home > Conditional Formatting > New Rule.
As a result, a dialog box will open up.
- At this moment, click Format only cells that contain from the Select a Rule Type box.
- Then, from the Format only cells with section, select less than or equal to from the second box.
- Later, type the following formula in the third box-
- After that, click Format.
Soon after, the Format Cells dialog box will appear.
- In the Fill section, pick your desired color.
I picked a red color.
- Finally, just press OK, and it will take you back to the previous dialog box.
- Just press OK.
Now have a look at the conditioned dates which are reminded with the red fill color.
3. Embed VBA Macro to Get a Pop-Up Alert for Due Date
If you like to work with codes, you can easily set pop-up reminder for due dates. The amazing part is you will get the reminder in a pop-up notification box including buyer names.
- Right-click on the sheet title.
- Then select View Code from the Context menu.
Soon after, a VBA window will open up.
- Write the following codes in it-
Option Explicit Sub Due_Date() Dim DueDate_Col As Range Dim Due As Range Dim PopUp_Notification As String Set DueDate_Col = Range("D5:D9") For Each Due In DueDate_Col If Due <> "" And Date >= Due - Range("D11") Then PopUp_Notification = PopUp_Notification & " " & Due.Offset(0, -2) End If Next Due If PopUp_Notification = "" Then MsgBox "No need to chase any buyer today." Else: MsgBox "These buyers need chasing today: " & PopUp_Notification End If End Sub
- Finally, just press the Run icon to run the codes.
Have a look, a pop-up notification message box is giving an alert with the buyer names.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
I hope the procedures described above will be good enough to set a due date reminder in Excel. Feel free to ask any question in the comment section and please give me feedback.