Forum Discussion
Power Automate for Date Difference using SharePoint Column Values
Active_Array The way I would do this is shown below. There are probably far more elegant ways but this is just a quick example and, also, I like a series of compose actions in my flows because I find it easier to identify where there are any problems.
This is my list where I have a choice column with the number of days for the renewal notices. You used "3 months" as one of the choices but it is much easier if you keep everything consistent, so I have used 90 days. There will be 4 emails due to be sent today for Los Gallardos, Bedar, Vera and Corvera.
In your flow add the recurrence schedule trigger and get the items from your list. To prevent a performance warning set the filter query to only bring back items that have an expiry date greater thgan or equal to today:
Initialise a floa variable. This will hold the renewal notice days later on.
Next, add a compose to get today's date with formatDateTime(utcNow(),'yyyy-MM-dd') - I always do date comparisons with yyyy-MM-dd format.
Next, add an Apply to each and select value from the dynamic content box. Inside it add 2 more compose actions, the first to get the expiry date from the dynamic content box, and the second to format that date into yyyy-MM-dd format.
Next, add 2 more compose actions inside the apply to each:
- The first converts the output of the RenewalNotice compose into a float with float(outputs('RenewalNotice'))
- The second converts that value to a negative with mul(outputs('floatRenewalNotice'),-1)
Then add a Set variable action and for the Value field select the outputs of the ConvertNegative compose.
Next, we want to calculate the date to send the email so we need to add an addDays expression and add the outputs of the ExpiryDateFormat compose and the varNotice variable which, as it's now a negative, has the effect of taking the number of days away from the ExpiryDateFormat compose. Because the addDays expects an integer we convert the variable to an integer then format the whole lot to yyyy-MM-dd format:
formatDateTime(addDays(outputs('ExpiryDateFormat'),int(variables('varNotice'))),'yyyy-MM-dd')
Next, although we've done everything in yyyy-MM-dd format, for my email content I want it in UK dd/MM/yyyy so add another compose and set the input to formatDateTime(outputs('ExpiryDateFormat'),'dd/MM/yyyy')
Next, we need to check if the SendEmailOn and ComposeToday are equal. So add a condition. Leave the red if no channel empty and add your email action into the green if yes action.
I received the 4 emails as expected:
Come back with any questions or if something isn't clear.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Active_Array The way I would do this is shown below. There are probably far more elegant ways but this is just a quick example and, also, I like a series of compose actions in my flows because I find it easier to identify where there are any problems.
This is my list where I have a choice column with the number of days for the renewal notices. You used "3 months" as one of the choices but it is much easier if you keep everything consistent, so I have used 90 days. There will be 4 emails due to be sent today for Los Gallardos, Bedar, Vera and Corvera.
In your flow add the recurrence schedule trigger and get the items from your list. To prevent a performance warning set the filter query to only bring back items that have an expiry date greater thgan or equal to today:
Initialise a floa variable. This will hold the renewal notice days later on.
Next, add a compose to get today's date with formatDateTime(utcNow(),'yyyy-MM-dd') - I always do date comparisons with yyyy-MM-dd format.
Next, add an Apply to each and select value from the dynamic content box. Inside it add 2 more compose actions, the first to get the expiry date from the dynamic content box, and the second to format that date into yyyy-MM-dd format.
Next, add 2 more compose actions inside the apply to each:
- The first converts the output of the RenewalNotice compose into a float with float(outputs('RenewalNotice'))
- The second converts that value to a negative with mul(outputs('floatRenewalNotice'),-1)
Then add a Set variable action and for the Value field select the outputs of the ConvertNegative compose.
Next, we want to calculate the date to send the email so we need to add an addDays expression and add the outputs of the ExpiryDateFormat compose and the varNotice variable which, as it's now a negative, has the effect of taking the number of days away from the ExpiryDateFormat compose. Because the addDays expects an integer we convert the variable to an integer then format the whole lot to yyyy-MM-dd format:
formatDateTime(addDays(outputs('ExpiryDateFormat'),int(variables('varNotice'))),'yyyy-MM-dd')
Next, although we've done everything in yyyy-MM-dd format, for my email content I want it in UK dd/MM/yyyy so add another compose and set the input to formatDateTime(outputs('ExpiryDateFormat'),'dd/MM/yyyy')
Next, we need to check if the SendEmailOn and ComposeToday are equal. So add a condition. Leave the red if no channel empty and add your email action into the green if yes action.
I received the 4 emails as expected:
Come back with any questions or if something isn't clear.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
The OData filter seems to be having issues though, any ideas what might be causing this:
****
The expression "Contract End Date ge '2022-10-14T01:58:04.5678018Z'" is not valid. Creating query failed.
clientRequestId: 06047386-18e5-4c44-a390-604df8f520a9
serviceRequestId: 06047386-18e5-4c44-a390-604df8f520a9
****
See my implementation below. It's similar to Rob's in that it's using number of days for the Renewal Notice.
The Document Library (below) has End Date, Renewal Notice Days (Choice field) and Owner (Person). All the fields are mandatory as we need them in our flow. The internal name for End Date is EndDate, and Renewal Notice Days is RenewalNoticeDays. Owner would be the person you would send the email to, informing them that their policy is ending soon.
The main flow is below. I'll go into each of the actions.
Recurrence is set to run at 1AM each day using my Brisbane time zone.
Convert time zone converts the current UTC date to my Brisbane time zone and formats it as yyyy-MM-dd.
Get files (properties only) gets all the files where the End Date is greater than today as we don't want past/expired policies.
Filter array filters our list to only policies that should have a reminder sent out. It uses a bit of a nested expression, so I'll break it down.
We want to end up with a formatted date in the format yyyy-MM-dd for our comparison with today's date (Convert time zone), so we wrap everything in a formatDateTime expression.
Within there we use addDays to subtract the Review Notice Days from the End Date. To do this we need to convert it to an int (number) since it's a choice field, then multiple it by -1 so it subtracts instead of adds).
formatDateTime(addDays(item()?['EndDate'], mul(int(item()?['RenewalNoticeDays/Value']), -1)), 'yyyy-MM-dd')We then compare that to the current date (Convert time zone).
We can then just iterate over the items in the Filter array and send out the renewal notice reminders. For this we can use an Apply to each using the Body of the Filter array. And the email can extract the relevant fields including the Owner's email, Filename with extension, Due Date (formatted), etc.
To get the properties you can just use the following expression format:
items('Apply_to_each')?['INTERNAL_COLUMN_NAME']To get the Owner's email you would use:
items('Apply_to_each')?['Owner/Email']Example email sent out is:
