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
Best Answer
-
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, ""))….
Answers
-
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, ""))….
-
It took some tweaking with the parentheses & commas, but yes, that worked! thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!