Help with complicated formula

=IF(OR([Phase Start Date]@row > DATE(2023, 2, 28)), [Phase End Date]@row < DATE(2023, 2, 1), 0, IF(AND([Phase Start Date]@row <= DATE(2023, 2, 28), [Phase Start Date]@row >= DATE(2023, 2, 1)), (NETWORKDAYS([Phase Start Date]@row, DATE(2023, 2, 28)) / 5) * [Resources Needed Per Week]@row, IF(AND( [Phase End Date]@row>=DATE(2023, 2, 1),[Phase End Date]@row<=DATE(2023, 2, 28)), (NETWORKDAYS(DATE(2023, 2, 1),[Phase End Date]@row) / 5) * [Resources Needed Per Week]@row, (NETWORKDAYS(DATE(2023, 2, 1),DATE(2023, 2, 28)) / 5) * [Resources Needed Per Week]@row,)))

The formula above is meant to calculate resources per month with some stipulations on when the phase start and end dates are. It is coming out as #unparseable. Can anyone help me out with what may be causing this.

Find more posts tagged with

Accepted answers

Mike TV

@Austun Bates

=IF(OR([Phase Start Date]@row > DATE(2023, 2, 28), [Phase End Date]@row < DATE(2023, 2, 1)), 0, IF(AND([Phase Start Date]@row <= DATE(2023, 2, 28), [Phase Start Date]@row >= DATE(2023, 2, 1)), (NETWORKDAYS([Phase Start Date]@row, DATE(2023, 2, 28)) / 5) * [Resources Needed Per Week]@row, IF(AND([Phase End Date]@row>=DATE(2023, 2, 1),[Phase End Date]@row<=DATE(2023, 2, 28)), (NETWORKDAYS(DATE(2023, 2, 1),[Phase End Date]@row) / 5) * [Resources Needed Per Week]@row, (NETWORKDAYS(DATE(2023, 2, 1),DATE(2023, 2, 28)) / 5) * [Resources Needed Per Week]@row

All comments

Mike TV

@Austun Bates

Austun Bates

Thank you @Mike TV. What was the issue? Was it the parenthesis at the end?

Mike TV

@Austun Bates

You'd have to closely compare the two formulas but basically you have extra parenthesis in some places and other places you needed two parenthesis but only had one. Also at the very end you had a comma without anything but closing parenthesis after it which I think can cause an error.

I also wasn't sure how many closing parenthesis would be needed without carefully counting all the open and close parenthesis so I just left all of the closing parenthesis off at the end because SmartSheet will automatically fill those in for you if you leave them off. It's better than having one too many at the end which will cause the formula to fail.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.