Formula help please

Fisher_K

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

Paul Newcome

Try wrapping it in a MIN like so:

=MIN(1, original_formula)

Fisher_K

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

Paul Newcome

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

Genevieve P.

Hi @Fisher_K

Here's the article on the MIN function to help explain:

https://help.smartsheet.com/function/min

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?

Fisher_K

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!

Paul Newcome

@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.

Fisher_K

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

Paul Newcome

@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.