How to calculate the Actual Duration only when the Actual Start and Actual Finish have values?

Sean B

Hi,

 

I'm working on a Smartsheet of which two of the columns are "Actual Start" and "Actual Finish". I have a third column called "Actual Duration (days)". Both the "Actual Start" and "Actual Finish" columns are date type columns and "Actual Duration (days)" is a Text/Number type column. What I want to have happen is as follows:

 

I'd like that whenever a user enters both the "Actual Start" and "Actual Finish" dates that the "Actual Duration (days)" gets auto-populated with the number of work days between them. However, if the "Actual Start" or "Actual Finish" are blanks, then I want the "Actual Duration (days)" column to also be blank and not have some formula error because it is missing parameters. 

 

Can someone help me write such a formula?

 

Thank you,

Sean

Christine Oakes

Sean

 

You can use an IF(And statament checking both for Isdate in both fields. If true do your calculation otherwise "".

 

Or - =IF(AND(ISDATE([Actual Start]1), ISDATE([Actual Finish]1)), [Actual Finish]1 - [Actual Start]1, "")

 

Chrisitne