Special formula help

Stuart.d

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

 

Andrée Starå

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 + Days@row

Please see the attached link/screenshot for more information.

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

Stuart.d

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

Stuart.d

I think I've cracked it! 



Thanks for your amazing help! 

Andrée Starå

Happy to help!

Great that you figured it out.

By the way, regarding the form question, you asked previously. Heres another excellent solution. https://www.formstack.com

Best,

Andrée