Update "Days Remaining" column in SharePoint List

[Jayneel12]

Hello! 

I am trying to create a PowerAutomate flow to update the "Days Remaining" a Calculated Column, every day at midnight, to reflect the # of days away it is from the due date. Currently, in my list settings, the "Days Remaining" column has the following formula "=IF(ISBLANK([Due Date]),"",IF([Due Date]-TODAY()<=0,"Past Due",[Due Date]-TODAY()))" and the "Due Date" is MM/DD/YYYY. However, the formula does not update daily with today's date and shows an incorrect number. Below is the outline of the flow I am aiming for, but I can not get it to work.

Any help would be appreciated. Thank you!

[creativeopinion]

@Jayneel12 The formula you are using should be used in a Calculated column.

If you'd like to use Power Automate to update a single line of text or number column with a calculation you perform in Power Automate, you can use an expression instead. However, in your Update Item action, you'll need to update a single line of text or number column—not a calculated column. 

Does your Due Date column include a time as well or just the date?

In the meantime, you might find this YT Tutorial helpful: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

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

[Jayneel12]

Hi @creativeopinion ,

So, the formula I am using is in my Calculated column in Lists. However, I am running into the issue that the value outputted for this column is not updated daily as it approaches the due date. For example, if a task is due in 16 days, when I recheck my List tomorrow, it still shows 16 instead of 15. Would it be possible to build in PowerAutomate if I change the column type from calculated to a single line of text? 

The due date column only includes a date, no time. 

[creativeopinion]

@Jayneel12 You can customize the automation to suit your needs—just depends on what your requirements are. Is it necessary to display the number of days in your list because you need to actually see this number?

Or, do you want to build an automation based on when the due date of an item is. I have a tutorial that covers how to send an email notification based on a date column in SharePoint: Send Emails Based on a 📆 Date Column in SharePoint with Microsoft Power Automate

You can customize the flow to suit your requirements. If you'd prefer to send a Teams message—replace the Send an Email action with a Post a Message in a Chat or Channel action. 

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

[Jayneel12]

Hi @creativeopinion,

Yes, displaying this number in my list is necessary, so I would like to try to set up an automation that updates this value daily.

However, if that is not possible,  I will check out the video you linked on automating a message based on the date column. 

Thank you! 

[creativeopinion]

@Jayneel12 If you want to update the column on a daily basis with the number of days left, you can absolutely do that. You can use an expression:

div(sub(ticks(EndDate), ticks(TodaysDate)), 864000000000)

I would recommend using a Compose action to store the expression. Just replace the text in blue with the relative dynamic content in your flow. Run a test before you use the output of the Compose action in your Update Item action.

If you are using the utcNow() function in your flow to get the current date and time, ensure that you are using the Convert Time Zone action as I've demonstrated in the tutorial to ensure you are getting your local time.

Hope this helps.