Hi,

I'm hoping smartsheet community can help me with an automatic formula.

We have a column called "Priority Deal" this is a symbol selection of the stars, where we rate a specific land opportunity (empty/one/two/three/four/five)

I'd like to create a formula when a specific star is selected, the column "Deadline Date" is automatically populated with a date based on the below critera,

So for example :

Empty = No date

one Star = Five weeks from current date

two stars = Four weeks from current date

Three stars = Three weeks from current date

Four stars = Two weeks from current date

Five starts = One week from current date

Is this possible?  Extra columns can be added if it means we can get his working.

I look forward to responses.

Kind Regards

Stuart

Functionality
Industry

Hi Stuart,

Try this.

Add two new columns, Deal Date and Days.

The Deal Date is the date that you want to count the deadline from. The today formula will update to the present day so that won't work.

Will it work that you don't put a date if it's no stars and no deadline?

The formula in the Days column (you can hide this when done) Right now it's just the number of days but you could change it to reflect workdays or something else if needed:

=IF([Priority Deal]@row = "Empty"; ""; IF([Priority Deal]@row = "One"; 35; IF([Priority Deal]@row = "Two"; 28; IF([Priority Deal]@row = "Three"; 21; IF([Priority Deal]@row = "Four"; 14; IF([Priority Deal]@row = "Five"; 7))))))

Formula in the Deadline Date column:

=[Deal Date]@row + [email protected]

https://cl.ly/cd6261

Depending on your country you’ll need to exchange the semi-colon to a comma in the above formula.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

In reply to by Andrée Starå

Hey Andree!

Love that you have shared the little video. Great work. Hopefully you can help fix it.

I'm still having trouble, I'm based in UK England if this helps arrange formula in anyway.

Regards

SD

I think I've cracked it!

Thanks for your amazing help!