Due Date Pop-up Alert in Excel | Date Due Tracker – Formula & Due Date Color Change Notifications

 

In this video tutorial I demonstrate how to create date sensitive alerts or notifications in Microsoft Excel. My worksheet tracks invoice due dates and I need it to notify me when an invoice is overdue.

Click here to download the featured file.

Method 1: Use a Formula

Click here to view this part of the video.

You can use an IF formula to notify you that an invoice is due.  In this example the IF formula returns “Yes” if the invoice is due in two or less days.

=IF(AND(D3<>””,TODAY()+$H$2>=D3), “Yes”,”No”)

Where H2 contains the number of days before the invoice is due that you want to receive the notification and D3 is contains the date that the invoice is due.

Method 2: Use Conditional Formatting

Click here to view this part of the video.

The logical test that we used in our IF formula is the same logical test we can use for conditional formatting.  Follow these steps to apply the conditional formatting:

1. Copy the logical test: AND(D3<>””,TODAY()+$H$2>=D3) on to the clipboard
2. Select the cells that you want to apply the conditional formatting to (the date due cells)
3. On the Home tab of Excel’s ribbon, click the Conditional Formatting button and select New Rule from the menu
4. Select the rule type: Use a formula to determine which cells to format
5. Type an equals sign in the box below the text Format values where this formula is true:
6. Use CTRL V to paste the logical test after the equals sign
7. Click the Format button and select the format you want to apply for due invoice dates
8. Click on OK to confirm your formats

Method 3: Use a VBA Macro

Click here to view this part of the video.

You can also use a VBA macro to notify you if an invoice is due. To use this method you will need to paste the code below into the Visual Basic Editor (VBE).

1. Open the VBE with the shortcut ALT F11
2. In the Project Explorer select ThisWorkbook (make sure this is in the correct project ie within the correct workbook)
3. Paste the code below into the code window
4. The code will run automatically when you open the workbook.

Private Sub Workbook_Open()


Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String


Set DateDueCol = Range("D3:D100") 'the range of cells that contain your due dates

For Each DateDue In DateDueCol

    'Change H2 to the cell for bring forward reminder days in your data
    If DateDue <> "" And Date >= DateDue - Range("H2") Then
    'Change the offset value to pick up the invoice number column in your data
        NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -2)
    End If

Next DateDue

If NotificationMsg = "" Then

    MsgBox "You do not need to chase any invoices today."

Else: MsgBox "The following invoices need chasing today: " & NotificationMsg

End If


End Sub