Forum Discussion
date diff for power auto for 2 column
I am creating a flow that will automatically send an email once a week if the dates between 2 columns become < 30 days.
I created a switch where if the project is Closed, nothing happens. if it is open it will get the items of the 2 values, but unsure how to do a date diff.
Do i do another switch where if above 30 nothing happens?
Also, if I have a switch that if it doesn't match does nothing is there a standard practice to close any potential loops? ty
nerdyplayer you don't really want a switch for this, a normal condition will work fine. Where the difference between the start and end date is less than 30 days and the Status is not Closed and the start date is in the future then we send the email, so in my list below this will only be Guadeloupe, Curacao and Aruba.
The flow has a recurrence schedule trigger set to run once a week at 4am on Monday. The get items actin has a filter query so we're only looking at items where the start date is later than today:
Add an apply to each and add 3 compose actions, 1 for the start date, 1 for the end date and one to calculate the difference. The expression for the date difference in days is:
int(split(dateDifference(outputs('ComposeStart'),outputs('ComposeEnd') ), '.')[0])Then add a condition and select the Outputs of the ComposeDifference compose action is less than 30 and the Status Value is not equal to Closed. Leave the red if no channel empty, but add your email action int the green if yes channel.
This will send the email about each open item where the difference is less than 30 days:
There are ways to send just a single email but this should get you going for now.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
5 Replies
nerdyplayer you don't really want a switch for this, a normal condition will work fine. Where the difference between the start and end date is less than 30 days and the Status is not Closed and the start date is in the future then we send the email, so in my list below this will only be Guadeloupe, Curacao and Aruba.
The flow has a recurrence schedule trigger set to run once a week at 4am on Monday. The get items actin has a filter query so we're only looking at items where the start date is later than today:
Add an apply to each and add 3 compose actions, 1 for the start date, 1 for the end date and one to calculate the difference. The expression for the date difference in days is:
int(split(dateDifference(outputs('ComposeStart'),outputs('ComposeEnd') ), '.')[0])Then add a condition and select the Outputs of the ComposeDifference compose action is less than 30 and the Status Value is not equal to Closed. Leave the red if no channel empty, but add your email action int the green if yes channel.
This will send the email about each open item where the difference is less than 30 days:
There are ways to send just a single email but this should get you going for now.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)- YAY it worked ty.
Question, under get items what is the reasoning behind the filter?nerdyplayer it's always more efficient to have a filter query rather than bringing back every item from the list. IN this case I just wanted items with a start date greater than or equal to today.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
Wrap both dates on both sides of the conditional with the ticks() function. Ensure the values that you wrap the ticks() function around are timestamps.
For example:
ticks(formatDateTime(variables('Todays Date'), 'yyyy-MM-ddTHH:mm:ss')) is greater(less) than ticks(formatDateTime(variables('The Other Date'), 'yyyy-MM-ddTHH:mm:ss'))
