Posted by: EPM Partners on July 3, 2014
The “[Status Date]” field in MS Project, often used by Project Managers to place a “stake in the ground” for progress tracking, can prove very useful in date-related formulas.
A common scenario is to focus on a fortnight planning window when maintaining your project schedule. A Project Manager will set the “[Status Date]” (via Project tab > Project Information > Status Date date-picker) to represent the start of the next 2 weeks planning. Then utilise other custom field(s) with formulas / graphic indicators to filter tasks to show only what is relevant for the next fortnight.
But how do we automatically calculate a date 2 weeks ahead without using a clumsy interactive dialogue box? A simple and effective approach is to just add 14 days to the “[Status Date]” field – and include this into your tracking custom field formulas.
IIf([Status Date]=ProjDateValue(“NA”),ProjDateValue(“NA”),DateAdd(“d”,14,ProjDateValue([Status Date])))
The key challenge to overcome is that “[Status Date]” needs to be checked if it has been set yet, otherwise the formula will return an #ERROR. If its value is not “NA” it then needs to be converted to a date-time data-type before being used in the DateAdd() function.
Hope this helps in your next MS Project schedule – happy formula writing!
