I need to set up a column that displays calendar days so that the project team can see the overall duration in addition to the working days. When I use the DateDiff("d",[Start],[Finish]) formula it works, however any task line or space in he schedule shows #ERROR, Is there a formula that can be used to eliminate this error message? Thank you!
-
1Thank you for posting this solution, Marv. It works great in MSP2013, applying it as a modification to the Text 1 field. I had to add +1 to the previous formula for the "Text" formula stated above, otherwise, a 3-day duration activity was showing a 2-day duration as the difference. IIf(isdate([Start]) And isdate([Finish]),datediff("d",[Start],[Finish]),"")+1 I applied the summary level task radio button to get the roll up of calendar days. – user26048Sep 28, 2016 at 0:47
1 Answer
I don't know about 2010, but in 2013 it generally works well unless you have a task row that does not yet have valid start and end dates, in which case you get #ERROR.
In that case, I set the custom field type to "Text" and used the following formula:
IIf(isdate([Start]) And isdate([Finish]),datediff("d",[Start],[Finish]),"")
If you absolutely must have the field type as numeric then using:
IIf(isdate([Start]) And isdate([Finish]),datediff("d",[Start],[Finish]),0)
...will avoid the display of #ERROR but does mean that all task rows have a numeric value.