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?

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
Check out the Formula Handbook template!