Others

How to Track SharePoint Item Duration and Highlight Overdue Requests

Tauseeq Magsi Sep 28, 2026 868 views

Question details

Users need to measure how long items in a SharePoint list remain open or active and visually highlight those that exceed a specific duration threshold.

How to Track SharePoint Item Duration and Highlight Overdue Requests

Product: SharePoint, Power Automate
Device & OS: not provided
Scenario: Managing incoming requests and measuring resolution time or tracking active status duration for overdue alerts.
Observed behavior: The list currently tracks basic metrics, but requires automated calculation of open duration and visual highlighting for overdue items based on status transitions.

Before you start

Ensure you have 'Edit' or 'Design' permissions for the SharePoint list and the appropriate Microsoft 365 licensing to create Power Automate flows.

Solution 1Recommended

Use Power Automate and Conditional Formatting

This solution uses a Power Automate flow to calculate the exact duration between status changes and utilizes SharePoint's built-in conditional formatting to highlight items exceeding your threshold.

Because SharePoint calculated columns cannot dynamically update using the current date (using [Today] is unsupported and causes errors), Power Automate is required to calculate the duration of items that are still open.

The workflow will trigger daily or upon modification to update a numeric 'Duration' column, which SharePoint will then evaluate for color-coding.

Create Date and Number Columns

In your SharePoint list, add a Date and Time column named 'Date Opened', a Date and Time column named 'Date Closed', and a Number column named 'Duration Days'.

Set Up the Power Automate Flow

Open Power Automate and create a 'Scheduled cloud flow' to run daily. Add the 'Get items' action pointing to your SharePoint list.

Calculate the Duration

Add an 'Apply to each' loop for the SharePoint items. Inside the loop, use a Condition to check if the item is still Open. Use the 'ticks' expression in a Compose action to calculate the difference between the current date (for open items) or 'Date Closed' (for closed items) and the 'Date Opened'.

Update the SharePoint Item

Add the 'Update item' action to write the calculated number of days into the 'Duration Days' column.

Apply Conditional Formatting

Navigate back to your SharePoint list. Click the 'Duration Days' column header, select 'Column settings', and choose 'Format this column'. Apply a conditional formatting rule to highlight the cell red if the value is greater than your required threshold.

Use Power Automate and Conditional Formatting

Alternative Trigger Option: If you only need duration calculated when an item's status actively changes (e.g., from Open to Closed), you can use the 'When an item is created or modified' trigger instead of a scheduled daily flow.

Free Microsoft Office alternative

Manage Project Timelines and Overdue Tasks Easily with WPS Spreadsheet

While SharePoint handles list automation online, WPS Spreadsheet offers powerful offline functions to track project timelines, calculate durations, and apply conditional formatting without complex automated flows. It is a highly compatible, free alternative to Microsoft Office.

1. Set up your tracker: Open WPS Spreadsheet and create columns for 'Start Date', 'End Date', and 'Duration'.
2. Calculate days: In the Duration column, enter the formula =DATEDIF(A2, IF(ISBLANK(B2), TODAY(), B2), "d") to automatically calculate active days.
3. Highlight overdue tasks: Select the Duration column, navigate to 'Home' > 'Conditional Formatting', and choose 'Highlight Cells Rules' > 'Greater Than' to apply a red fill for overdue items.

Fully compatible with Microsoft Excel (.xlsx) formulas and formats.Easily calculate item durations using built-in DATEDIF functions without workflows.Apply custom conditional formatting to highlight overdue tasks instantly.Free, lightweight, and fast to install on any operating system.

Frequently Asked Questions

Can I calculate duration in a SharePoint calculated column without Power Automate?

Yes, but only for static dates. You can use a calculated column formula like =[Date Closed]-[Date Opened] to find the duration in days. However, it will not update dynamically for items that are currently open because using the [Today] function in calculated columns is unsupported.

How do I change the color of an entire row for overdue SharePoint items?

In your SharePoint list, click your current view name at the top right (e.g., 'All Items'), select 'Format current view', choose 'Conditional formatting', and create a rule based on your duration column's value. This applies the format to the entire row rather than a single column.

Does Power Automate require a premium license to update SharePoint lists?

No, standard Power Automate features used for triggering flows from SharePoint and updating list items are included in most base Microsoft 365 enterprise and business licenses.

Office Quick Solutions