How to Create a Microsoft Project Formula for Late, Soon, or OK Milestones
Question details
The user needs a custom text field formula to automatically categorize project milestones as 'Late', 'Soon', or 'OK' based on Start date, Date1, and the Status Date.
- Product
- Microsoft Project
- Device & OS
- not provided
- Scenario
- Setting up automated text indicators for project schedules to quickly identify the urgency and status of upcoming milestones.
- Observed behavior
- Requires a working formula that accurately processes missing dates ('NA'), evaluates upcoming items within the next month, and avoids syntax errors caused by standard logic functions like 'And'.
Ensure that your project schedule has an active 'Status Date' set (found under the Project tab), as the formula requires this baseline to calculate 'Late' and 'Soon' statuses.
Apply a Nested IIf Formula in a Custom Text Field
Use a specialized nested IIf formula that properly handles Date1 'NA' values and evaluates milestones dynamically against the Status Date.
Microsoft Project custom formulas cannot always handle complex 'And/Or' operators efficiently when dealing with empty date fields. Instead, a series of nested IIf statements provides the cleanest logic.
The formula first checks if Date1 has data. If it does, the milestone is 'OK'. If not, it checks if the Start date is over a month away ('OK'), before the Status Date ('LATE'), or within the approaching month ('SOON').
Right-click on any column header in your Gantt Chart view and select 'Custom Fields' from the context menu.
In the Custom Fields dialog, ensure 'Task' is selected at the top, change the Type dropdown to 'Text', and select an available field (e.g., Text1). Click 'Rename' to call it 'Milestone Status'.
Under the 'Custom attributes' section, select 'Formula'. In the formula box, paste the following exactly: IIf([Date1]<>projdatevalue("NA"),"OK",IIf([Start]>=DateAdd("m",1,[Status Date]),"OK",IIf([Start]<[Status Date],"LATE","SOON")))
Click 'OK' to save the formula. Check the box 'Use formula' under the 'Calculation for task and group summary rows' section if you want this to apply to summary tasks as well, then click 'OK' to close the dialog.
Manage Projects Effectively with WPS Spreadsheet
While Microsoft Project is powerful, it can be complex and expensive for many teams. If you are looking to track milestones, build Gantt charts, or automate status indicators, WPS Spreadsheet offers a free, lightweight, and highly flexible alternative. You can replicate advanced project logic using standard spreadsheet functions in a familiar interface.
- 1. Download WPS Office: Install WPS Office for free from the official website and open WPS Spreadsheet.
- 2. Set Up Your Project Tracker: Create columns for Task Name, Start Date, Due Date, and Status.
- 3. Apply Spreadsheet Formulas: Use simple spreadsheet formulas like =IF(B2<TODAY(),"Late","OK") to instantly track your project milestones without dealing with complex MS Project custom fields.
Frequently Asked Questions
Why is my formula returning a syntax error in Microsoft Project?
Syntax errors often occur due to regional settings in Windows. If your system uses a comma as a decimal separator, you must use semicolons (;) instead of commas (,) to separate the arguments in your Microsoft Project formula.
What does projdatevalue("NA") do in the formula?
In Microsoft Project, an empty date field internally evaluates to 'NA'. The projdatevalue function safely converts the text string 'NA' into Project's internal date format so the formula can verify whether a date has been entered in the Date1 field without crashing.
How do I change the 'Soon' timeframe from one month to two weeks?
To change the 'Soon' threshold, modify the DateAdd portion of the formula. Change DateAdd("m",1,[Status Date]) to DateAdd("d",14,[Status Date]), which will flag tasks starting within the next 14 days as 'SOON'.
