In this article, we explain how to set a reminder in Excel for expiry dates with Lido. Simply follow the steps below.
Below we outline the steps on how you can set a reminder in Excel for expiry dates using Lido:
If you haven't already signed up for a Lido account, register for free at: https://www.lido.app/go/signup
Lido acts as a dynamic spreadsheet that streamlines your workflow by integrating spreadsheet data with email functionality. To automate sending expiration reminder emails, input essential details such as recipients, expiration dates, and email content.
Once you've created your Lido file, transfer your Excel data into Lido by copying and pasting it, then convert it into a table.
Each row should correspond to an expiration reminder email you intend to send. If the recipients vary for each reminder, include their email addresses in your table (consider column C in the example below). Otherwise, if you're sending emails to the same individuals (e.g., yourself or your team), you can skip this step.
Sample expiration reminder email data. Column C is optional; exclude it for consistent recipients in each reminder.
expiration reminder excel template
Select the range, right-click, and choose "Convert Table from Range."
Assign a name to your table and save it.
If you've set up your data table in Lido, you're ready to proceed. If not, go back and either input the data directly into Lido or copy-paste it from Excel.
Expiration reminder emails should precede expiration dates. Let's assume we want to send reminders two days before the expiration date. The formula is straightforward:
reminder_date = expiration_date - 2
Here, the expiration_date refers to the cell containing the expiration date. If the expiration dates are in column C, with the first expiration date in cell C2, then the formula becomes:
=C2-2
Press enter to generate a computed column. This specialized column type in Lido automatically applies the formula to each row, which is useful when you add more entries to your table.
Let's name this column "Send Date."
Now, let's craft the subject and body of the expiration reminder email.
Begin by creating templates using your table's column names as placeholders, indicated by [@Column] syntax.
Create templates for email subject and body in a new worksheet as follows:
Then merge these templates with your table data from the other tab to make the email content dynamic. Achieve this by creating two new computed columns for Subject and Body, utilizing the STRINGTEMPLATE() formula within a computed column.
Start a new computed column and label it Subject.
Apply this formula in row 2 of the Subject column:
```=STRINGTEMPLATE(Sheet1!$B$1)```
In our example, Sheet1!$B$1 represents the location of our subject email template. Make sure you adjust this to match your cell location if it's different; otherwise, the formula won't work. Also, remember to anchor the cell reference with $, ensuring all rows use the same template cell.
You should now have a subject column displaying different expiration deadlines based on the values in column C.
Repeat the process for the email body. Create another computed column and apply the formula below, adjusting the cell references to align with your sheet:
```=STRINGTEMPLATE(Sheet1!$B$2)```
You'll end up with something like this:
Lido's spreadsheet features a unique formula type called Action formulas, which can send data externally but require manual triggering to execute. One such action formula is SENDGMAIL, enabling direct email dispatch from your spreadsheet.
The SENDGMAIL formula works as follows:
=SENDGMAIL(sender, recipient, subject, body, [status_cell], [cc], [bcc], [attachments])
Here's our basic SENDGMAIL formula:
=SENDGMAIL(<sender-credential>, B2, E2, F2)
Add logic for when to dispatch the expiration reminder email
To display SENDGMAIL only when today's date matches the reminder send date, include an IF statement:
=IF(TODAY() = reminder_send_date, SENDGMAIL(sender, recipient, subject, body))
Here's how the formula works:
If today's date matches the reminder_send_date, display the SENDGMAIL formula; otherwise, display FALSE.
Let's create a new computed column and input our formula below in row 2 (remember to adapt the formula for your data). Rename the column to "Send Reminder" by double-clicking on the header cell.
=IF(TODAY() = D2, SENDGMAIL(<sender-credential>, B2, E2, F2))
As observed, the SENDGMAIL formula appears only for dates when dispatch is due.
You've now established a dynamic expiration reminder email system, ready to automate your Gmail expiry date reminders!
Right-click on the SENDGMAIL formula and select "Run Action" to send the email. You will be prompted to authenticate into your Gmail account the first time you send an email from Lido.
Now, let's automate the execution of the "Send Reminder" column. For rows where SENDGMAIL appears, Lido can automatically execute them once daily, even if the file is closed.
Access the menu for your "Send Reminder" column and choose "Run column on a schedule."
Select the preferred time of day for sending your messages, then confirm your selection.
From now on, at your designated time (let's say 1:15 PM or your chosen hour), Lido will automatically review if there are any pending expiration reminder emails scheduled for dispatch, where TODAY matches the reminder_send_date. If such reminders exist, Lido will execute each SENDGMAIL formula, initiating the email dispatch.
Impressed? Take the next step with Lido by clicking here!
We hope that you now have a better understanding of how to set a reminder in Excel for expiry dates.
