How to Set a SharePoint RAG Status Based on a Finish Date
Question details
The user needs to automate a RAG (Red, Amber, Green) status column in a SharePoint list based on the proposed finish date and task completion status.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Tracking project deadlines and automatically updating visual status indicators in a SharePoint list.
- Observed behavior
- List items need to dynamically show Amber two days before the deadline, Red on the deadline, and remain Red until the task is marked as Completed.
Ensure you have edit permissions for the target SharePoint list and access to create scheduled cloud flows in Microsoft Power Automate.
Use a Scheduled Power Automate Flow
Create a daily scheduled flow in Power Automate to check dates and automatically update the status column.
Because SharePoint calculated columns only update when an item is manually modified, a scheduled Power Automate flow is required to check the finish date against the current date every day.
Navigate to Power Automate, click 'Create', and select 'Scheduled cloud flow'. Set the recurrence to run once every day.
Add the 'Get items' action and select your SharePoint site address and target project list.
You can use an OData filter query in the 'Get items' action (e.g., Status ne 'Completed') so the flow only processes active tasks.
Add an 'Apply to each' loop for the values. Inside it, add a 'Condition' to check if the 'Finish Date' is equal to today or two days away, using Power Automate date expressions.
In the 'If yes' branch of your condition, add an 'Update item' action. Set the RAG status column to 'Red' or 'Amber' accordingly.
Apply JSON Column Formatting for Colors
Apply custom column formatting in SharePoint to visually display the Red, Amber, and Green colors based on the text value updated by your flow.
Track Project Deadlines Easily with WPS Office
While SharePoint and Power Automate handle complex automated lists, sometimes a straightforward spreadsheet is the most efficient way to manage a project. WPS Office offers a free, lightweight alternative to Microsoft Office, allowing you to track project statuses, apply conditional formatting for RAG indicators effortlessly, and manage deadlines without complex workflows.
Frequently Asked Questions
Can I calculate RAG status using only SharePoint calculated columns?
Yes, you can use an IF formula in a calculated column to check the finish date against the current date. However, calculated columns only update when the item is manually modified. If you need the status to change automatically as the deadline approaches without user interaction, a scheduled Power Automate flow is required.
How do I add background colors to my SharePoint status column?
Click the column header in your SharePoint list, navigate to Column settings > Format this column, and select 'Choice pills' or 'Advanced mode'. From there, you can define specific colors (like red, yellow, and green) for each status value.
Why is my Power Automate flow failing to update the SharePoint list?
This commonly happens due to formatting mismatches between dates. Ensure your condition correctly formats the dates for comparison (e.g., using the formatDateTime function) and that the flow has the appropriate connection permissions to edit items in the SharePoint site.
