In Microsoft Excel, you can automatically send expiration alerts based on a specified date, which is especially useful when you manage a customer or user database with specified dates to cancel or execute programs repeatedly.
This way, every time you enter an assessment document, you’ll see the due date highlighted, which will make it easier for you to take action, as the case may be. We’re sure you’ll be interested in learning how to do this in an Excel document, so keep reading.
What procedure should I follow to calculate due dates and create alerts in Excel?
Generate alerts for your upcoming academic exams, remind you to send communications to your clients, or Learn about deadlines from pay In your invoicing, the due date feature in Excel really offers great utility if you know how to take advantage of it.
Coming to the procedural problem of calculating future or due dates, it is necessary to have a data sheet with certain identifying items. This should include the name, start date and due date.
Table Settings for Calculating Due Dates
Anyway, the first requirement to achieve this is to download Excel from their website and install it on your computer or mobile device, then you have to launch an Excel window. You must have at least three columns, indicating the ID, and then one column for the initial or issue date and another column for the expiration date.
Next is to have an alert or due date column where the text will be displayed and the colors you configure to highlight those dates in the document. The following, We will create a copy of the document To deal with it, you would do this via the «Save As» button located in «File», type in the assignment «Enable Excel Books for Macros».
Create alerts in Excel
You should know that you can track payments through the form, for example, although you can adjust it as needed, you can facilitate the organization of your data.It’s important Data is sorted and sorted This will allow you to continue creating alarms or reminders in Excel.
- Next is to include a helper column to calculate the days to due, which you can then hide.
- A cell containing the current date «=TODAY()» is also handy.
- In the first cell of the helper column, enter the formula that subtracts the due date from the current date, anchoring the second value with a «$» sign before the letters and numbers.
- In the alert column, you will enter a conditional formula with an «IF» function, for this example, let’s assume the first cell of the helper column is E3. The structure will be «=IF(E3<0, "DUE", IF(E3=0, "DUE TODAY", IF(E3<4, "DUE SOON", "LESS "&E3&" DAYS")))".Press Enter and drag to the end of the table
What you’re telling Excel is that it depends on Days remaining until expiration Date (calculated in the auxiliary column), showing descriptive text.
How to create expiry alerts with different colors in Excel?
In Excel, you can customize various aspects of cell formatting, viewing the date and time (in milliseconds) or assign a specific color to each. In the case of due dates, it is strongly recommended that you set a color to identify them more easily, to do so, continue with the previous example:
- Select the range of cells containing the alert, click the Conditional Formatting button and choose New Rule.
- In the dialog box, select Use a formula to determine which cells to format.
- In the ribbon where you enter the formula, place «=$E3<0", press the Format button and choose your preferred color combination, then click Accept.
- You use the conditions «=$E3=0», «Y(=$E3>0, $E3<4)” y “=$E3>4″, each assigned a different color combination.
How to place automatic alerts in Excel documents using macros?
You’ve noticed that alerts in Excel change color and text when you modify the data for the release date or due date.Now, let’s do one last configuration so that we can Show popup notification When opening a document:
- First select the entire table, go to «Insert» and click «Table», check if the table has a header and accept it.
- Outside of the table, you’ll enter a formula to calculate «due» and «due today» invoices. For the structures «=COUNT.IF(daystodue),»<0") and "=COUNT.IF(daystodue),"=0")", suppose for this case the result is in F3 AND F4.
- Right click in a blank cell and select «View Code», in the new window go to the right panel and click «ThisWorkBook». You will see two drop-down lists in the upper part, open the first and select «Workbook», in the second «Open».
- In the main box, enter between the two lines:
- condition1 = range(«F3»).value
- Message 1 = «Deadline:» & Condition 1
- Condition 2=Range(“F4”).Value
- Message2=»Date due today:» & Condition 2
- MsgBox(Message1&vbNewLine&Message2)
- You save your changes in Visual Basic, close the file, open it again, and you’ll see the ready alert message.