Traffic-Light Status
This calculated column uses a SharePoint formula to return a Red, Amber, or Green status based on how close the due date is. Because it lives in a calculated column, SharePoint fills the value in for every item automatically - no custom development and nothing for your users to type.
How It Works
- What it does: Gives a Red, Amber, or Green status based on how close the due date is.
- What you get: Red once overdue, Amber within the next 7 days, Green beyond that.
- If a value is missing: If Due Date is blank it is treated as very old and shows Red, so it is best to give every item a due date.
Common Use Cases
- At-a-glance health on tasks or projects
- Deadline-risk dashboards
- Portfolio status views
- Traffic-light reporting
Details
- Formula Type: Calculated Column
- Formula Category: Status & Progress
Required Columns
| Display name | Column type |
|---|---|
| Due Date | Date and Time |
| Traffic-Light Status | Calculated |
Prerequisites
- Create the column the formula reads: Due Date
- Add or edit a few items so there is data for the formula to work with
How to Apply the Formula
- Add a column, choose More…, then select Calculated (calculation based on other columns)
- Set the data type returned from this formula to Single line of text
- Paste the formula below into the Formula box
- Click OK to create the column
Formula
=IF([Due Date]<[Today],"Red",IF([Due Date]<[Today]+7,"Amber","Green"))Common Questions About Traffic-Light Status Formula
What does this formula do?
Red / Amber / Green health flag. It lives in a calculated column, so SharePoint fills the value in automatically for every item.
Which columns do I need to set this up?
You need: Due Date (Date and Time). The formula reads these to produce its result.
Can I change the formula for my own columns?
Yes. Swap the column names in brackets for your own, and adjust any numbers or text.
If LookBook 365 is code-free and out-of-the-box, why does this example use a formula?
A SharePoint formula is native, declarative configuration, not custom development. It only works with the item's own fields and simple logic - it cannot run scripts, reach external services, or change other data, and nothing is deployed to your tenant. It keeps working through Microsoft updates. If you would rather not set this up yourself, this is exactly the kind of thing I configure for clients.
Traffic-Light Status Formula Example