HOW TO SET AN ALARM IN EXCEL

How to set an alarm in Excel

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

techpady virtual classroom

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

Dr. Ehoneah Obed

Pharmacist | AWS Cloud Practitioner | Full Stack Developer | Content Creator

Post navigation

Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *