Automate Flow date & time incorrectly triggered

[Justin_V]

I've created a basic task tracker in SharePoint List with columns "Status" and "Due Date." The status is meant to transition to "Breached" when the task surpasses its due date based on the Automate Flow I've created. However, I'm currently encountering an issue where the status switches to "Breached" even before reaching the due date.

Due to our region locality, we are using the date format of dd/MM/yyyy hence I converted the UTCNow in my Automate Flow to this formatDateTime(utcNow(), 'dd/MM/yyyy HH:mm tt'). The issue occurs only when the due date is at the start of the month (first week of the month). I do not encounter this issue when the due date is in the middle or at the end of the month. Please help

[creativeopinion]

@Justin_V Can you share a screenshot of your full flow? It'll help to provide more insight. 

In the meantime, you might be interested in this YT Tutorial: 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!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Justin_V]

Here's the flow I'm using to trigger the Status change when Due Date is passed.

[creativeopinion]

@Justin_V Is your plan to run your flow every minute? I would recommend you first filter out the items from your SP list where the due date has passed before you loop through them. This would help to make your flow more efficient. The way your flow is currently set up, you are returning all items from your list and you are running a condition check on each item. 

Do you have a filter query in your Get Items action?

[Justin_V]

I've configured the filter query to specifically search for items with statuses marked as Open or In Progress, excluding those labeled as Closed. While I can adjust the recurrence to a more extended timeframe, my primary concern lies in the Status automatically switching to Breached even when the item hasn't surpassed its Due Date.

[creativeopinion]

@Justin_V

You should be able to filter on your date column as well and only return the items where the date is greater than today.  

Create a New Flow with a Manual Trigger. You can replace this later with a recurrence trigger.

Get Today's Date

Add a Convert time zone action to your flow. Whenever you are using dates and times in your flow—it's best practice to use a Convert Time Zone action. In the base time field, insert the utcNow() expression.

Source Time Zone: Coordinated Universal Time

Destination Time Zone: Local Time Zone

Format String: Round Trip

Get Items Filter Query

Adjust your filter to include filtering out items where the due date column (in my case it's date) is lt (less than) the converted time. If you'd like to include items with today's date—you can use the le (less than or equal to) operator.

You will need to use the internal column name of your due date column. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.

Ensure you have single quotes around the Converted time dynamic content. 

Just test out this first filter query—you can adjust the filter query after you've run a test.

Return a Count of Items

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Verify the number of items returned are those where the due date has already passed. 

Adjust your Filter Query

I would recommend you add each argument to your Filter Query and run a test. Use the Compose action to verify that the number returned is what you are expecting. This way you can identify which part of your query is incorrect or needs adjusting. 

It looks like you are running additional condition checks in your original flow to see if the status is not equal to 'Closed' (I don't believe this is necessary since you are already using that argument in your filter query. However, I would add the additional condition to check if it's not equal to 'Escalated to Microsoft'. 

Condition Check

Your condition check should be used to check if there are any items to update. In the first value field, insert the output from the Compose action above. Change the operator to is not equal to. In the second value field enter a 0.

Put your Update Item action into the Yes branch.

You might be interested in this YT Tutorial: How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

IN THIS VIDEO:

✅ How to send multiple list items in a single email with a Power Automate Flow

✅ How to create a dynamic date range

✅ How to use the Convert Time Zone action

✅ How to use a Filter Query in the Get Items action

✅ How to count number of items in an array

✅ How to use the Select action to extract a users display name and email address

✅ How to create a unique list of email addresses

✅ How to use the Create HTML Table action

✅ How to customize the HTML Table with CSS styles

✅ How to use the Send an email (V2) action

✅ How to use the Append to String Variable action

✅ How to create a custom list of items for an email

✅ How to use the Send an email (V2) action

✅ How to display singular or plural text based on the number of items returned

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok