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

Christina Reid

Hello!

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)

David Joyeuse

Hi @Christina Reid

What you need is an IF formula, not a SUMIF. SUMIF will add cells depending on specific criteria.

So you should use this instead:

=IF(ISBLANK([Actual Affiliation Date]@row), [Date Assigned to Coach]@row - TODAY(), [Date Assigned to Coach]@row - [Actual Affiliation Date]@row)

Hope it helped!

David Joyeuse

Hi @Christina Reid

What you need is an IF formula, not a SUMIF. SUMIF will add cells depending on specific criteria.

So you should use this instead:

=IF(ISBLANK([Actual Affiliation Date]@row), [Date Assigned to Coach]@row - TODAY(), [Date Assigned to Coach]@row - [Actual Affiliation Date]@row)

Hope it helped!

Christina Reid

Worked like a charm - thanks!

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)