Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Options
Sean B
Sean B
edited 12/09/19 in Archived 2016 Posts

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

Comments

  • 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

     

     

This discussion has been closed.