Error in Filter Query for Sending Notification 3 Days Before Date

(0) Share

Report

Posted on by CU29111912-0

I have a Power Automate flow that should send an email 3 days before a specific date.
I created a SharePoint list where the StartDateAndTime column contains the dates for which I want to receive a notification 3 days in advance.

In the Filter Query field, I entered the following code:

StartDateAndTime gt '@{formatDateTime(utcNow(), 'dd.MM.yyyy HH:mm')}' and StartDateAndTime lt '@{formatDateTime(addDays(utcNow(), 3), 'dd.MM.yyyy HH:mm')}'

When I test the flow, I encounter an error, and I’m not sure what’s causing it. Could it be the date format in the column, the filtering logic, or something else?
For additional context, the StartDateAndTime column is configured as a Date and Time type.

Does anyone know how to correctly configure the Filter Query so it works as expected?
Thank you in advance for your help!

Categories:

Business Process Flows

All responses (7)

Answers (0)

creativeopinion 9,897 on at

Like (0)

Report

Error in Filter Query for Sending Notification 3 Days Before Date

@CU29111912-0 The logic of your flow needs to be adjusted. The output of your compose action that contains today's month and day is outputting utcNow() (aka the current date and time) in your selected timezone (since you've converted it.

There are a couple of problems with this:

1) Date/Time Format:

In my original response I indicated that your date time needs to be formatted in ISO 8601 format yyyy-MM-ddTHH:mm:ssZ.

2) Filter Array Argument

If you review the input of the data from the Get Items action (highlighted below) you can see the output of the StartDateAndTime. Your argument is incorrect and the way you've formatted will never return any results. Unless the item was created at exactly the same time that you ran your flow.

Additionally, SharePoint stores all timestamps in UTC.

First, you need to compose the datetime range you want to filter out. Once you have that you can add in the rest of your actions in your flow. In your case, you need to compose the following:

- 3 Days from Today at 12 AM

- 3 Days from Today at 11: 59 PM.

The filter array action needs to be formatted with the argument to look for any items from your SharePoint list that are greater than 3 Days from Today at 12 AM and less than 3 Days from Today at 11: 59 PM.

Follow this section of my YT Tutorial on how to get a date 3 days from a date stored in SharePoint.

In this tutorial, I only need the date (not time). However, you will just need to adjust the expression (example below):

For 12AM: formatDateTime([datetime],'yyyy-MM-ddT00:00:00)

For 11:59PM: formatDateTime([datetime],'yyyy-MM-ddT11:59:00)
CU29111912-0 4 on at

Like (0)

Report

Error in Filter Query for Sending Notification 3 Days Before Date

I give two screen
1) flow structure
2) test result

The date column is taken from the lists, and there it has a date or time declaration in the settings.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Suggested answer

KaptainLady 2 on at

Like (0)

Report

Error in Filter Query for Sending Notification 3 Days Before Date

Is the date column a calculated column? If so, that is isn't supported in Power Automate. Instead of using that column, you could use the due date column and set the filter to due date is equal to today's date +3.
creativeopinion 9,897 on at

Like (0)

Report

Error in Filter Query for Sending Notification 3 Days Before Date

It would help provide more insight to see a screenshot of your full flow—the logic is unclear at the moment.
CU29111912-0 4 on at

Like (0)

Report
Error in Filter Query for Sending Notification 3 Days Before Date

I followed the instructions you provided and added an hour to the date, but I’m still facing an issue. I’m using the "Filter Array" action in Power Automate, where I retrieve the date from a column and compare it with the current date. The output is 0, even though the day, month, year, hour, and minute are the same.

Does anyone know why the comparison isn't working, even though the values seem identical? Could it be related to the date format, time zones, or something else?

formatDateTime(body('Convert_time_zone'), 'yyyy-MM-ddTHH:mm:ss.fffffff')
lbendlin 7,828 on at

Like (0)

Report

Power Automate: Error in Filter Query for Sending Notification 3 Days Before Date

Your date math needs to be based on ISO-8601 strings.
creativeopinion 9,897 on at

Like (0)

Report

Error in Filter Query for Sending Notification 3 Days Before Date

@CU29111912-0 First issue is with how you've formatted the date. It should be 8601 format yyyy-MM-ddTHH:mm:ssZ. Secondly, you may want to consider the logic of your filter query. In your post, you've indicated you want to send a notification 3 days in advance. Your addDays() function has a positive number which will add three days not subtract it from your StartDateTime.

I would recommend using Compose actions to ensure you output the correct timestamps first before using them in your filter query.

You might be interested in this YT Tutorial below. Although this specific tutorial uses a date only column, the concepts covered in this tutorial can be used in your flow.

Send Emails Based on a 📆 Date Column in SharePoint with Microsoft Power Automate

In this Microsoft Power Automate tutorial, I’ll show you how to build a flow that will send a Happy Birthday email to a user based on a date column in a SharePoint list. The SharePoint list also contains a column with a Manager’s name which we’ll use to send a three-day and day of reminder to the user’s manager.
This automation will use the Filter Array action to filter out all SharePoint list items where the user’s birthday is today or in three days. This flow can apply to a variety of scenarios such as:

📅 Student Birthdays
📅 Project Due Dates
📅 Contract/Membership Renewals
📅 License Expirations
📅 Client Anniversaries

IN THIS VIDEO:
✅ How to Send an Email based on a Date Column in SharePoint
✅ Using the Recurrence Trigger in Power Automate
✅How to Use the Filter Array Action with multiple conditions
✅ How to Get Dynamic Content from a Filter Array Action
✅How to Get a Date Three Days from Today
✅ How to Create a Dynamic Date Based on utcNow()
✅ How to Return a Count of Items
✅ How to initialize and set a variable
✅ How to use the Send an Email (V2) action
✅ How to send test emails

Hope this helps!

Consider giving me a ❤️ if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram