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

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)

Find more posts tagged with

Formulas

Accepted answers

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!

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.