Hello there, we have a task to send email reminders to our clients to settle the payment for our services if any of the following two conditions are met:
- the number of months of outstanding payment is three or above
- the month(s) of outstanding payment is 3 months or more (for example, the current month is June. If the month of outstanding payment is 2021 March or before, we need to send reminders)
Please check the attached photo for easier explanation. Column A-C is the data table. Column E&F are just for your reference to explain which row meets the condition to send the reminders.
I know how to send the reminders if the conditions are met. My question is: how to set the code for each row to tell whether the conditions are met? It is easy to tackle the first condition. Any number higher than 2 would meet the condition to send the reminder. However, the tricky part is the second condition. As you can see from the photo Column C, there is a set date format used - yyyymmm or yyyymmmm. And if there are two or more months, it will be separated with a symbol like semicolon or comma.
The flow should be - for each row → if first condition is met → send reminder; if first condition is not met → check second condition → if second condition is met → send reminder; if both conditions are not met → proceed with next row