Formula if a cell is blank, run one formula, if not blank run another formula



I want to track the amount of days a person has been in each stage of their transition – specifically Pre-boarding and Onboarding. I wrote the below formula not thinking that once Pre-Boarding ended and Onboarding started that the formula would keep calculating as I used TODAY. 

What I need is a formula that says “If there is no date in [Actual Affiliation Date]1, calculate total number of days of [Date Assigned to Coach]1 vs TODAY but if there is a date in [Actual Affiliation Date]1, instead calculate total number of days of [Date Assigned to Coach]1 and [Actual Affiliation Date]. 

I messed around a bit and thought I got close but it actually would only return zero so I'm sure I'm much further off then I thought.

=SUMIFS(ISBLANK([Actual Affiliation Date]1), [Date Assigned to Coach]1 - TODAY(), [Date Assigned to Coach]1 - [Actual Affiliation Date]1)


Best Answer


  • Christina Reid
    Christina Reid ✭✭✭✭

    Worked like a charm - thanks!

  • Sam29
    Sam29 ✭✭

    @David Joyeuse I am trying to complete a similar formula, but not use a calculation. I am getting an invalid column value, however. Can you help?

    Trying to have the formula look at a Revised Due Date column and if it is blank to pull the Due Date column value. If it is not blank, pull the revised due date column value.

    =IF(ISBLANK([Revised Due Date]@row), [Due Date]@row, [Revised Due Date]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!