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

Tags:

• ✭✭✭✭✭✭

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.

• Employee

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?

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.

• edited 10/13/22

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!