Help! trying to figure out a formula for dates!
Hi! I am trying to create a formula for dates. I have start date and end date which are 70 days apart & i need to have a formula that calculates the % complete, based on the start date and current date....
I have tried and tried to figure this out but i am stuck!
Thank you!!!
Answers
-
You would take the number of days elapsed and divide it by the total number of days.
=(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)
-
Thank you! I have been looking at one of your other formulas today! I got an invalid operation....?
=(TODAY() - [Ignite Start Date]@row) / [Ignite Finish Date (10 weeks)]@row - [Ignite Start Date]@row)
-
DOH! I just saw i missed a bracket! ok that works! How can i then cap it so that if it is over, i can set to 100%?
ps - thank you!!!!
-
To cap it at 100% you would include a MIN function so that it pulls the lowest value either 100% or the formula's output.
=MIN((TODAY() - [Ignite Start Date]@row) / ([Ignite Finish Date (10 weeks)]@row - [Ignite Start Date]@row), 1)
-
This is awesome - thank you!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!