Incorporating ISDATE into Formula to average the time between various dates if conditions are met

I have a formula for calculation of a row by row average of review cycles, where each row can have 3 review cycles.

I'm trying to figure out how to incorporate the "ISDATE" Function into this formula such that if one of the review cycles is unnecessary and a user enters "N/A" the formula will still calculate correctly.

Currently I have:

=AVG(IF(ISDATE([First Cycle End]@row), [First Cycle End]@row, "") - IF(ISDATE([First Cycle Start]@row), [First Cycle Start]@row, ""), IF(ISDATE([Second Cycle End]@row), [Second Cycle End]@row, ""), - IF(ISDATE([Second Cycle Start]@row), [Second Cycle Start]@row, "") ……………)

But I'm getting a syntax error

Find more posts tagged with

Formulas

Accepted answers

jaimelynn0814FEB

Does this work?

=AVG(IF(AND(ISDATE([First Cycle Start]@row), ISDATE([First Cycle End]@row)), [First Cycle End]@row - [First Cycle Start]@row, ""),IF(AND(ISDATE([Second Cycle Start]@row), ISDATE([Second Cycle End]@row)), [Second Cycle End]@row - [Second Cycle Start]@row, ""),IF(AND(ISDATE([Third Cycle Start]@row), ISDATE([Third Cycle End]@row)), [Third Cycle End]@row - [Third Cycle Start]@row, ""))….

All comments

jaimelynn0814FEB

Does this work?

kss5229

https://community.smartsheet.com/discussion/comment/440786#Comment_440786

It took some tweaking with the parentheses & commas, but yes, that worked! thanks!

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.