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.
Best Answer
-
=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
Answers
-
=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
-
Thank you @Mike TV. What was the issue? Was it the parenthesis at the end?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …