Forum Discussion
Solved
Power Automate for Date Difference using SharePoint Column Values
Hi everyone,
I have a SharePoint document library with columns:
- "End Date": A date column.
- "Renewal Notice": A choice column with options:
- "7 Days"
- "14 Days"
- "3 Months"
The goal is to check the end date, calculate the amount of days from today to the end date, compare the result to the renewal notice option, and if it matches send a notification email to a predefined email address.
I found a date difference expression for Power Automate:
div(div(sub(ticks(outputs('End Date')),ticks(outputs('Today'))), 10000000), 86400)
I'm unsure how to use the value from SharePoint in this expression. If I attempt the column name "End Date" it throws an error. Maybe I need to compose a variable first, but still unsure of how that would fit into the expression. Maybe my syntax is incorrect or perhaps another avenue must be used entirely.
So to elaborate the flow:
1. Runs daily at 1am
2. Pulls the values from the document library
3. Calculates the amount of days from today to the end date
4. Compares if result is equal to the amount in Renewal Notice. (May need to change choice values from string to integers only which is fine)
5. If equal, send email notification. Else do nothing.
Any help is greatly appreciated.
Thanks,
AA
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- I changed the Get Items to Get Files (Properties only) as it is a document library.
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:
This is fantastic and looks to be exactly what is needed. I'm tied down for a little bit but will test this out later this week. I'm going to mark this answer in the meantime but will reach out if anything pops up during testing. I really appreciate the detailed response, thank you kindly.
