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!

  • 1
    Thank 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. ![enter image description here](i.stack.imgur.com/wgQnC.jpg)
    – user26048
    Sep 28, 2016 at 0:47

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.