As Project doesn't have a TODAY() type calculation similar to Excel we use the status date to set the reference date. This is set in the Project menu or using the key combination of <ALT><P>,<P>,<S>.
Sometimes it is useful to only have the week number relative to the status date, for instance to enable easy filtering to answer the request "show me everything that is finishing in the next 4 weeks". At other times it is useful to have a text field to group by. This blog will show both methods and explain how to chose between start and finish dates.
Both method require a calculation in a custom field. To get to the custom field dialogue simply right mouse on the column headings and chose "Custom Fields", select one and rename it and then copy the calculation into dialogue that displays when you click on "Formula"
Simple week number
To display a simple number for the weeks till the start or finish of each task:
- Start date: datediff("ww",[Status Date],[Start])
- Finish date: CStr(1+datediff("ww",[Status Date],[Finish]))
This displays positive numbers for tasks in the future or negative for those in the past. This allows you to easily filter on start or finish week. The field can be either a number or text field
Text field
To display a more complex text field, which is useful when grouping, with easier to read entries use the following formula. Note that these group week 0 into "This week" and all items in the past into "Previous weeks".
- Start date: IIf(CStr(datediff("ww",[Status Date],[Start]))<0,"previous weeks",IIf(CStr(datediff("ww",[Status Date],[Start]))=0,"this week","Week " & CStr(datediff("ww",[Status Date],[Start]))))
- Finish date: IIf(CStr(1+datediff("ww",[Status Date],[Finish]))<0,"Previous Weeks",IIf(CStr(1+datediff("ww",[Status Date],[Finish]))=0,"this week","Week " & CStr(1+datediff("ww",[Status Date],[Finish]))))
It is now simple to group by the new custom field to re-order the plan by week start or finish.
