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

kss5229

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

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, ""))….

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, ""))….

kss5229

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

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