Formulas & functions not working
Hello,
I am attempting to create a % Completion formula to track a projects completion b/w start and end dates. I cannot seem to make the functions work in the cells. Is this because I haven't upgraded to the full license or am I doing something wrong?
Please advise,
Matthew
Best Answer
-
Sorry about that. I found an error - missing paren. Try this:
=IF(AND(ISDATE([start date]@row), ISDATE([end date]@row)), IF([start date]@row>today(), 0, IF([end date]@row<today(), 1, NETWORKDAYS([start date]@row, today()) / NETWORKDAYS([Start date]@row, [end date]@row))), 0)
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @Matthew Salama ,
Does your license allow you to enter other formulas or only data?
If you can enter other formulas, please attach a screenshot of your sheet and the formula that's not working. Remove or hide any sensitive information.
Happy to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
This is what the formula is giving me as a % total. Theoretically it should be 100%. How can I show this and make the argument valid for the remainder of my sheet?
Note: some project deadlines have not yet lapsed.
-
Hi @Matthew Salama ,
I'd adjust your formula to prevent errors. Use:
=IF(AND(ISDATE([start date]@row), ISDATE([end date]@row)), IF([start date]@row>today(), 0, IF( [end date]@row<today(),1, NETWORKDAYS([Start Date]@row, today())/NETWORKDAYS([Start date]@row, [end date]@row)), 0)
This will work as a column formula. The formula says that if a date is missing the % complete is 0%. If the start date is in the future % complete is 0%. If the end date is in the past % complete is 100%. Otherwise calculate the % complete.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
-
Sorry about that. I found an error - missing paren. Try this:
=IF(AND(ISDATE([start date]@row), ISDATE([end date]@row)), IF([start date]@row>today(), 0, IF([end date]@row<today(), 1, NETWORKDAYS([start date]@row, today()) / NETWORKDAYS([Start date]@row, [end date]@row))), 0)
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thank you so much @Mark Cronk for your amazing help!
-
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!