27 Mar A small formula for my friends in Finland
<Tazza (and Led Zeppelin (Dancing Days) on the radio, thank goodness for BBC 6 Music)>
So, you know who you are, and I said I’d write this up… For everyone else, this is a simple formula with graphical indicators to show the “Deadline cushion” between a deadline date and the Finish date of a task. Also, I’ll include the “deadline cushion” visually in the Gantt chart (as well as the Entry table).
1st off, we did this in MSProject 2007. I haven’t tested in 2010, 2003, 2002 etc, but it should work just the same.
1. Insert a task level custom field of type Number (eg Number 1), change the name (Deadline Cushion) and customise it with the following formula.
IIf([Deadline]<>4294967295,(Projdatediff([Finish],[Deadline])/[Minutes Per Day]),999999999)
2. This formula is pretty basic, Deadlines are set (by default) at 17:00, milestones are set (by default) to finish at 08:00 – therefore sometimes you’ll notice that even though tasks finish on the same date, the times might be different and hence the formula will not return a zero. You could easily check for this. Also, assuming that there is not a deadline, the formula returns 999999999 (a large number) rather than an error.
3. We can display a graphical indicator based on the returned value. The tests are run sequentially, so make sure they are entered in the order below. Note that there is no Image for the test “equals 999,999,999.00”
4. Once completed, we can test by putting the relevant deadlines in MS Project. I’ve highlighted the deadlines and tasks on the Gantt chart.
5. Finally, we can highlight the cushion value if it is –ve, by editing the bar styles as per the below. I’ve also included the deadline cushion value in the inside text field.
6. Finally, the display in the Gantt chart now shows…
Good luck and Enjoy…. 🙂