# Special formula help

Options
✭✭
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

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

• ✭✭
Options

I think I've cracked it!

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!