% Complete
I am trying to do a column where it calculates the % complete based on 4 other columns (that have to be completed in succession). Once a date is entered into these other columns then I would like the % complete to add 25% to the % complete.
I was thinking I need to do an Multiple IF statement, but cannot seem to figure out the formula.
Or if someone knows an easier way!
Best Answer
-
This formula would work assuming that the milestones are completed in sequence. Technically, it will allow you to "skip" a milestone. Meaning if don't add dates to Milestones 1-3, but there is a date in Milestone4 it will still result in 100%.
=IF(ISDATE([Milestone4]@row), "100%", IF(AND(ISDATE([Milestone3]@row), NOT(ISDATE([Milestone4]@row))), "75%", IF(AND(ISDATE([Milestone2]@row), NOT(ISDATE([Milestone3]@row))), "50%", IF(AND(ISDATE([Milestone1]@row), NOT(ISDATE([Milestone2]@row))), "25%", "0%"))))
Answers
-
-
This formula would work assuming that the milestones are completed in sequence. Technically, it will allow you to "skip" a milestone. Meaning if don't add dates to Milestones 1-3, but there is a date in Milestone4 it will still result in 100%.
=IF(ISDATE([Milestone4]@row), "100%", IF(AND(ISDATE([Milestone3]@row), NOT(ISDATE([Milestone4]@row))), "75%", IF(AND(ISDATE([Milestone2]@row), NOT(ISDATE([Milestone3]@row))), "50%", IF(AND(ISDATE([Milestone1]@row), NOT(ISDATE([Milestone2]@row))), "25%", "0%"))))
-
Thank you Jason. It worked!
😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!