cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcvoyager
Frequent Visitor

Using Calculated Date field to send an email reminder

Hello,

 

I have SharePoint list with a calculated date field - "EquipEndDate" which is "EquipStartDate" plus x number of days which the users enters. I would like to send an email reminder two days before the EquipEndate exipres.

Looks like I can't filter the query using - Calculated field  i.e. EquipEndDate.

So i am trying to use the below condition but i am always getting the input as false and it emails all the entires. Can you please let me know how this can be done?

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions

Hello @dcvoyager ,

that's a very nice calculated column. 🙂 Do you need the number of days or do you need just the result date?

Looking at the calculation I'd split it into multiple calculations in the flow depending on the [Duration] and store the calculated value in a variable (to use later in the condition). That means a 'Switch' action based on the [Duration] column, branches for: WEEK, DAY, MONTH, YEAR. In each of the branches calculate the 'EquipEndDate' with the expression addToTime(...) reference to that expression.

addToTime([EquipStrDate],[numberToAdd],'[timeUnit]','dateFormat')

example to add 1 week to a date stored in variable 'ExtendedDate':
addToTime(variables('ExtendedDate'),1,'Week','yyyy-MM-dd')

 

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

6 REPLIES 6
tom_riha
Super User
Super User

Hello @dcvoyager ,

you're using the 'EquipEndDate' in the 'Condition', isn't that the calculated column? I think you should use there the 'EquipStartDate' instead.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Thanks @tom_riha. 'EquipStartdate' is entered by the users and based on the number of days/weeks/months/years selected the 'EquipEnddate' gets calculated. I can't use the EquipStart date because the user selection will be different in every case.

Hello @dcvoyager ,

so you've got the 'EquipStartdate' and then the users enters a number of days in some column and you add the days to the 'EquipEnddate'? If so, you could move the whole calculation into Power Automate. There's an expression addDays(...) to add a number of days to a date.

addDays([dynamic content], number, 'yyyy-MM-dd')

Notes:
[dynamic content] is the EquipStartdate
number is the number of days to add to the EquipStartdate
'yyyy-MM-dd' is the format of the result date = you don't need the formatDateTime(...) expression, you can use just the addDays(...) instead


[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

The problem is user selection can be x weeks/x months etc...then I will have a formula to evaluate each selection and then convert into number of days. Not sure if Flow supports multiple if conditions.

This is my calculated EquipEndDate in List.

 

=IF([Duration]="WEEK",[EquipStrDate]+(EquipDuration*7),IF([Duration]="DAY",(EquipDuration+[EquipStrDate]),IF([Duration]="MONTH",DATE(YEAR([EquipStrDate]),MONTH([EquipStrDate])+EquipDuration,DAY([EquipStrDate])),IF([Duration]="YEAR",DATE(YEAR([EquipStrDate])+EquipDuration,MONTH([EquipStrDate]),DAY([EquipStrDate]))))))

Hello @dcvoyager ,

that's a very nice calculated column. 🙂 Do you need the number of days or do you need just the result date?

Looking at the calculation I'd split it into multiple calculations in the flow depending on the [Duration] and store the calculated value in a variable (to use later in the condition). That means a 'Switch' action based on the [Duration] column, branches for: WEEK, DAY, MONTH, YEAR. In each of the branches calculate the 'EquipEndDate' with the expression addToTime(...) reference to that expression.

addToTime([EquipStrDate],[numberToAdd],'[timeUnit]','dateFormat')

example to add 1 week to a date stored in variable 'ExtendedDate':
addToTime(variables('ExtendedDate'),1,'Week','yyyy-MM-dd')

 

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]
dcvoyager
Frequent Visitor

Thank you, Tom_Riha, will try it out

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,795)