Recognizing special format of dates and compare the difference of dates

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:

  1. the number of months of outstanding payment is three or above
  2. 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

1 Like

@wth1993 - For the Column C, below code will split the months and store it an array…


Then the array will be looped to check if its meeting the Month criteria

(cdate(item).Month <= now.AddMonths(-3).Month and cint(item.Substring(0,4)) = now.Year) OR cint(item.Substring(0,4)) < now.Year

My Input

My Output

Please find the attached workflow (45.5 KB)

Run the workflow as is, you will see the Output in the Input.xlsx as shown above.

Hope this helps…


Hello Prasath17, thank you for your input! However, it seems that I couldn’t run the workflow? It said unresolved activity

1 Like

@wth1993 - I have downgraded the packages to match your. Please check the attached workflow… (49.6 KB)

1 Like

Thank you. It works like a champ! I just have a question on the code because I am not familiar.

Could you explain how the following codes work?
→ StringSplitOptions.RemoveEmptyEntries? If there is a “&” to separate between months, will the code be like this: row(2).ToString.Split({",“c,”;“c,”&"c},StringSplitOptions.RemoveEmptyEntries)

->(cdate(item).Month <= now.AddMonths(-3).Month and cint(item.Substring(0,4)) = now.Year) OR cint(item.Substring(0,4)) < now.Year

1 Like

It Omit array elements that contain an empty string from the result. Please see the below referrence…

row(2).ToString.Split({",“c,”;“c,”&"c},StringSplitOptions.RemoveEmptyEntries) - This is correct…

(if the Month is less than 3 months from the current month and year is 2021) OR (Year is less than 2021) → Then send the email reminder…

If you are done with your testing and everything is working fine please mark my post as solution, that will close this thread and others will benefit from it.


Hello Prasath17, thank you for your explanation! A question on the following code:

(cdate(item).Month <= now.AddMonths(-3).Month and cint(item.Substring(0,4)) = now.Year) OR cint(item.Substring(0,4)) < now.Year

It is working fine now, but my concern is the second part

cint(item.Substring(0,4)) < now.Year

Although it works now, it might not work in the following scenario. Let’s assume the time now is January 2022, and there is a month o/s payment in 2021NOV or even 2021DEC. It will trigger the email reminder although the second condition is not met (only 2 months outstanding for 2021NOV or just 1 month outstanding for 2021DEC). I think for this part to be valid, the current months have to be in or after March. Am I right?

1 Like

You are 100% right…


Please change the if condition as shown below…this should cover all cases irrespective any year.

cdate(item) <= now.AddMonths(-3)

Thank you very much!


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.