There are two methods that can be used to set an alarm or reminder in the Microsft Excel application. The two methods are:
- Using conditional formatting
- Using a VBA code
We begin by using conditional formatting to set a due date reminder in excel by highlighting the due dates in any color of your choice and then generating an alert with the help of a VBA script.
How to use conditional formatting to set an alarm in Excel
We have a list of due dates above that need to be met as displayed above. The conditional formatting feature will help us to know these due dates by highlighting them in any picked color of our choice
Follow the steps below to guide you through it
- Click and select all the due dates in the column. Column B in our case
- Click on home
- Select conditional formatting from the Home tab.
- Select a new rule from the pop-up menu that appears.
- A new dialog box appears. In the box that appears, select Format only cells that contain from the Select a Rule Type box.
- Select t less than or equal to from the second box from the Format only cells with section.
- In the third box input =TODAY()+7
- Click on the format. The format cells dialog box will appear
- In the fill, section click on or select a color of your choice
- Click on OK
Click on OK
Now all the due dates will be highlighted as shown below in the color of your choice
Digital School Academy
Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials
How to use the VBA to get an alert before the due date
Right-click on the title sheet below the excel sheet
Select View code from the menu that appears. The VBA window will appear after that.
Input the following lines of code into the VBA window
[code]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 pay any bills today.”
Else: MsgBox “These bills need to be settled: ” & PopUp_Notification
End If
End Sub[/code]
This pop-up message will show showing the bills that need payment today
Using and, today & if functions to set due date reminder in excel
To get the due date reminder, we’ll create a formula. We’ll use the TODAY, AND, and IF functions to accomplish this. We’ll look for a date seven days ahead of the current date. The formula will produce a result, Yes if any date is after the due date; otherwise, No. To display the results, we added a new column E.
Follow the procedures below to achieve the desired outcome
- Select and activate cell E5
- Input into the cell the formula below
[code]=IF(AND(D5<>””,TODAY()+$D$11>=D5),”Yes”,”No”)[/code]
- Press enter
- After that, drag down the fill handle to apply the same formula to the remaining cells
How do we understand the formula above?
- TODAY() + $D$11>=D5
It will determine whether the date – 7 days ahead of the current date is greater than or equal to the date of Cell D5 or not. TRUE will be returned.
- “” D5>””
It will determine whether or not Cell D5 is empty and will return TRUE if it is.
- AND(D5>”,”TODAY()+$D$11>=D5)
The AND function will then combine both outputs. If any of the outputs are FALSE, it will return FALSE; if both are TRUE, it will return TRUE– TRUE.
- IF (AND(D5>”,”TODAY()+$D$11>=D5),”Yes”,”No”)
Finally, the IF function will return Yes if TRUE is returned and No if FALSE is returned.
Yes
Leave a Comment