Power Automate reminder using date in SharePoint calculated column
Posted On February 24, 2021
“The Power Automate flow works if I use a date formatted SharePoint column, but I need a reminder based on a calculated date from another date column.”
“The field ‘CalculatedDate’ of type ‘Calculated’ cannot be used in the query filter expression.”
When you calculate a date or a number of days in SharePoint, you might not want to end there. Sure, it’s nice to display the date/number of days in the list view, but you can take it one step further. You can build a Power Automate flow that would send a reminder on that date or after that number of days.
But once you try to use that calculated column in a Filter Query, Power Automate won’t like it. “The field ‘CalculatedDate’ of type ‘Calculated’ cannot be used in the query filter expression.” You can’t use calculated columns in the OData filter query. But you can rebuild the whole calculation into an expression, and use that expression in the Filter Query.
Building the Filter Query
You should take one step back and look on the formula in the calculated column. What do you calculate? Is it a number of days from a date until today? Or do you add time period to a date? Which existing date column you use?
Especially the last question is important. When building a Filter Query in Power Automate, you need some ‘fixed’ points. One of them is the ‘base’ date column as that’s a column you can use. The second is today’s date provided by the utcNow() expression (as you’ll check if today is the ‘reminder’ day). Every Filter Query must be build around them.
Number of days from a date
Let’s take the first situation, you want to send a reminder after a number of days since SharePoint item was created, e.g. 7 days. You might use the JSON column formatting to calculate the number of days in the list view with the following formula:
The full formula is explained in the post linked above, for this post it can be a bit simplified. @now is JSON placeholder representing today’s date:
= today - Created
Since you want to send the reminder on the 7th day, you can add 7 to the equation:
7 = today - Created
As already said, the filter must use the ‘base’ date, an existing date column. In this situation it’s the Created date that must be moved to the left side of the equation. That will give us the Filter Query to use:
Created = today - 7
Created eq 'addToTime(utcNow(),-7,'day','yyyy-MM-dd')'
Adding a time period to a date
The other situation is when you calculate a date in the calculated column by adding a time period. For example, you add 1 month to a Date date column and you want to send a reminder on that date. Let’s start here from the simplified formula:
= Date + 1 month
You want to send the reminder only if today is the date:
today = Date + 1 month
Again, the Filter Query must be based on a date column. Here we’ve got the column Date and it must be moved to the left side of the equation:
Date = today - 1 month
Date eq 'addToTime(utcNow(),-1,'month','yyyy-MM-dd')'
Power Automate doesn’t allow you to use calculated columns in the Filter Query, but it’s not a problem. Calculated column is just a formula using other columns, and as such you can replicate it from scratch in Power Automate.
This post is about replacing such calculated date column in the Filter Query with a ‘base’ date column and an expression. It is the main part of Power Automate reminder process, once you can filter the relevant items, you can send the reminder.
The second reminder flow is designed for Date and Time columns. With Date and Time columns you can’t use the ‘equals date’ operator as the time will be rarely equal. You must work with a range ‘less than date’ and ‘greater than date’ as you can see in the event reminder flow.