Using Calculated Date field to send an email reminder

[dcvoyager]

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?

[tom_riha]

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.

[dcvoyager]

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.

[tom_riha]

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

[dcvoyager]

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

[tom_riha · Accepted solution]

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

[dcvoyager]

Thank you, Tom_Riha, will try it out