Updating a SP Column Based on Todays Date

[Baffled_PA]

Hi,

I'm new to PA, but I have created a list in SP that has a due date column and a status column. 

I would like to to set up a PA that checks each day if the due date is today and if so, it updates the the status column accordingly.

I have 3 status, Due, Not Due and Overdue and the status column in SP in a calculated column with the following formula:

=IF([Due Date]=TODAY(),"Due",IF([Due Date]<TODAY(),"Overdue",IF([Due Date]-0<TODAY(),"Due","Not Due")))

which works fine and has RAG conditional formatting.

I would like to to be a schedule PA, each morning. I am in Dublin time zone and have my regional settings set to same.

I can do a PA which says it the flow succeeds, but it doesn't update the column and I don't know what I'm doing wrong!

Any help appreciated.

Thanks!

[creativeopinion]

@Baffled_PA You might find my tutorial helpful: Send Emails Based on a 📆 Date Column in SharePoint with Microsoft Power Automate

Although this tutorial specifically goes through how to send an email based on a date column. You can apply the same concept to your flow. Instead of a a Send an Email (V2) action, add an Update Item action to your flow and change the status to Overdue.

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

[wskinnermctc]

Can you be more clear about the columns you have? It seems like you have 2 columns "Due Date" and "Status" where the "Status" is a calculated column?

DueDate	Status
datetime type column that has the due date of item	Calculated column using formula to check due date

Does this appear correct?

If you only have a Due Date column and a calculated Status type column, the calculated column will only change whenever the item is updated. So you don't update a calculated column from Power Automate.

Note: a Calculated Column does not automatically update each day, the item has to be changed or edited in order to trigger the calculated column to update.

For example if you created an item today 30-Jan-2024 that has a due date of 02-Feb-2024 the calculated column would show "Not Due". But everyday after today the calculated column will show "Not Due" since the item has not been edited or changed. Even if you look at the item on 03-Feb-2024, the item will still show "Not Due".

Solution:

Create a Status column that has choices for each kind of deadline. These need to have clear values that can be used with the Power Automate logic. It is important to have a "Completed" or finished status so that Power Automate can filter out/ignore these rows since they won't need any kind of updating.

You can use other status columns for filters, but I included it in this example to be simple.

DueDate	Deadline Status
datetime type column that has the due date of item	choice type column with the choice options something like ("Due", "Overdue", "Final Day", "Completed")

You can do this a number of ways, but I'm separating them to make it easier to see:

• Get Items - that have a due date of today's date
  • Filter the Get Items by Status not equal "Completed" and a DueDate equal to today
• Update Items - so the status is "Final Day"

Have a second Get Items and Update for Overdue

• Get Items - that have a due date less than today
  • Filter the Get Items by Status not equal "Completed" and a DueDate less than today
• Update Items - so the Status equals "Overdue"

This may not be the precise flow to use, but it is a basic example. I have a SharePoint list that has tickets submitted, so there is a TicketStatus column that will say if it is Completed or need to be changed to Overdue.

Separate the Get Items by type of status that needs to be updatedGet Items - Todays Date - this has a Filter Query that checks if the DueDate is equal to todayThis will update the Status of the item to Final Day for items that have a due date of todayGet Items - Overdue - this has a Filter Query that gets items with a date Less Than lt today uctNow()This will update the Status of the item with Overdue

So this is a basic example above, but it should get you on the right track. There are many logical ways to Get Items or apply filters based on different types of Status. You will have to set up your SharePoint list and process in a way that accommodates you using different Status or dates to update the items.

I recommend not using a generic column name like "Status" as well as adding some sort of "Completed" or "Archived" status to the item to identify rows that are not longer needed. Instead of thinking of things as "Due" or "Not Due" since "Not Due" is vague. Does "Not Due" mean it hasn't reached the due date? I would assume something is Due all the way up until it's Due Date. 

Please go to the link for In-Depth Analysis into Get Items and Filter Queries for instruction example about how to use the Get Items and filter by columns.

[Baffled_PA]

Thanks for your help!

Yes, you're exactly right with how the columns were set up.

I have set up PA as below:

Get Items:

Update Item

However, when I run it I get this error message:

Thank you for the explanation re calculated columns!

[wskinnermctc]

The expression "Status ne and DueDate eq 31-01-2024" is not valid.

You do not have a Status value that is ne (not equal). You need to finish the filter expression Status is not equal to....what?

Status ne 'Completed'

and

DueDate eq '31-01-2024'

You also need to put single quotes around whatever value the Status is not equal (ne).

You need to put single quotes around the utcNow().

Go back and look closer at my example.

TicketStatus ne 'Completed' and DueDate eq 'utcNow('yyyy-MM-dd')'

Go to this link reference about Get Items and Get Files Filter Queries for another example.

[Baffled_PA]

Apologies, I thought I had it in right.

It's working now, thank you so much for your help!