Answered

Using Calculated Date field to send an email reminder

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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?

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

tom_riha 10,187 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hello @Anonymous ,

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
tom_riha 10,187 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hello @Anonymous ,

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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]))))))

Was this reply helpful? Yes No
Verified answer

tom_riha 10,187 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hello @Anonymous ,

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')

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you, Tom_Riha, will try it out

Was this reply helpful? Yes No