Formula help please
Hi community  I am using this formula in the 'Planned progress' column of my projects:
But it returns more than 100% when the date is past todays date (see example below). So I need to add to the formula  if today's date is after the planned end date, the planned progress should always be 100% (and never more than 100%).
I'm new to Smartsheets and i'm not sure how to achieve this. Any help would be appreciated  thankyou
Answers

Try wrapping it in a MIN like so:
=MIN(1, original_formula)

Thanks Paul  where in my formula do i insert that please? Thanks!

You would set it up how I have it in my last comment.

Hi @Fisher_K
Here's the article on the MIN function to help explain:
MIN will take the lowest value in either a range or listed values. In Paul's example, he put a 1 at the front (or 100%), then a comma, then you would add your entire formula.
This means that the MIN function will look at the outcome of your formula and either show it or the 1 depending on which is lowest.
=MIN(1, original_formula)
or
=MIN(1, IF(OR(ISBLANK(.....etc))
Does that make sense?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks both :)
Min works a treat  although now, when using =MIN(1, original_formula) 100% is inserted when the date fields are empty (and i would like the formula to return a blank in this instance). Any ideas please? thanks so much!

@Genevieve P. Thanks for providing the extra info and clarification. I definitely dropped the ball on that one.
@Fisher_K You would wrap everything in an IF statement that essentially says "IF the start date is not blank AND the end date is not blank then output the MIN."
=IF(AND([Planned Start Date]@row <> "", [Planned End Date]@row <> ""), MIN(1, IF(OR(ISBLANK(.....etc)))
This will ensure the calculation is only run on rows where both dates are populated.

Thankyou Paul.
That worked a treat.
Now my issue is that the formula returns 100% if the date in the planned start date is in the future.
Really appreciate the help,
Thanks, Kate

@Fisher_K In that case you would add another argument to the AND statement to say if the [Planned Start Date]@row is less than today.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 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!