How to Set Due Date Reminder Formula in Excel (2 Easy Methods)

We have Buyer in column B, Amount in column C, Due Date in column D, and Output in column E. We’ll use this dataset to set a due date reminder.

Method 1 – Using Combined Functions

Steps:

Insert the following formula in cell E5.

=IF(AND(D5<>"",TODAY()+$D$11>=D5),"Yes","No")

⏬ Formula Breakdown:

➤ TODAY()+$D$11>=D5

Checks 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<>””

Checks if the Cell D5 is empty or not and will return as-

Output: TRUE

➤ AND(D5<>””,TODAY()+$D$11>=D5)

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”)

The IF function will return Yes for TRUE and No for FALSE.

Output: Yes

Press Enter and use AutoFill to fill the rest of the column.

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, so they don’t need to be reminded.

Method 2 – Using Conditional Formatting

Steps:

Arrange the dataset like the first image, without the output boxes.
Select the date cells.
Go to Home and choose Conditional Formatting, then pick New Rule.

In the New Formatting Rule window, select Format only Cells that contain in the Select a Rule Type option.
In the Format Only Cells with option, select the less than or equal to option, insert the rule you want to use, and press the Format option.
We used =TODAY()+7, meaning that the cell will be formatted if the due date is less than a week from today’s date.

In the Format Cells window box, go to the Fill option and select the color you want to use, then press OK.

Press OK to use the set conditions on your worksheet.

The Due Dates for Pam, John, and Ron have been marked with the formatting color. This means they should be reminded about the due dates.

How to Set a Due Date Reminder Using Excel VBA

Steps:

Arrange the dataset like in the first image.
Go to the Developer Tab and select Visual Basic.

The VBA editor will appear.
Select Insert and choose Module to open a VBA Module.

Use 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