Help with a Formula
I have a Target % Complete column in a project plan template that essentially looks to see if you are tracking to the % complete you should be based on today's date and the start and end date of any task (or tells you that you are missing dates).
I've included the formula below that returns the result of what your target % complete should be. The thing is, when a task is indicated @ 100%, the cell with this formula goes blank. I want it to say 100% but can't tell where to update the formula to make this happen.
Any thoughts?
=IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row)))))), "Missing Dates")
Comments
-
It looks like all you’re missing is the “Value if False” part of the very first IF statement. You have instructions for what to do if the Status is not Completed, but there’s no indication of what to do if the Status is completed.
For example (without the encompassing IFERROR statement for clarity):
IF(Status@row <> “Completed”, {rest of your formula}, “100%”)
Let me know if this makes sense / works!
Cheers,
GenevieveJoin us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you - Conceptually I understand what is missing based on how you outlined it. My question is where you have {rest of your formula}....am I just adding the "100%" in quotes to have it return that value? I am not sure how to create the "rest of your formula"
-
My apologies for not being more clear - the {rest of your formula} indicated the rest of the formula that you have already written/created. I'll copy/paste it below.
In reviewing this, it looks like your column might already be set up as a percent column, is that correct? If that is the case, instead of saying "100%" (which would return that value as text), you can just add: 1. The 1 will equal 100% in a percent column.
I've bolded the only addition to your formula above (the , 1). Let me know if this works!
=IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row))))), 1), "Missing Dates")Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 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!