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