Formula Help

Lisa Welch

I have a column "current status" with drop downs 1., 2., 3., etc. Depending on which drop down is chosen I need a date calculated. ie. if 1. is chosen date column should be today +35 days. 

Thank you,

Kelly Moore

Hey @Lisa Welch

=IF([current status]@row =1, [date column]@row+35, IF([current status]@row =2, [date column]@row+x, IF([current status]@row =3, [date column]@row+y)))

If the value for 1 is not numeric but a text string, you will need to enclose the text in "quotes" like my example. Numbers should not have quotes. You will continue the pattern of IFs for each dropdown term. The number of closing parentheses should equal the number of IFs used.

This formula must go into a column formatted as a date column, not a text number formatted column.

Does this work for you?

Lisa Welch

Hi Kelly, We've added a variable. I would like to share my sheet with you , if possible. We have added a column for date entry of status update, then current status drop down which depending on which dropdown is chose will drive the best case worst case date.

1. Current status updated (date column)
2. Current status dropdown options, (at the end of each # is days ie. 50/35 being 50 days worst case, 35 best case)
3. Dropdown drive 1. + # of days

https://app.smartsheet.com/sheets/68qxm84MgCQQFhw7gG9jvJPXGgpwQG9542QhWcX1

Kelly Moore

Hey @Lisa Welch

Sorry for the slow response - it's been a hectic day. If I am understanding, you are wanting the columns Best Case Date to Plant and Worst Case Date used in two new helper columns - Updated Best Case and Updated Worst Case. The new formulas in these helper columns will key off the 2 values at the end of the different drop down response+ existing Best case/Worst case dates. That's how I'm building it and it should be easy to move the formulas if not quite right.

I noticed your dropdown responses 3 and 6 contained a '&' instead of a '/' . Is this significant to the formula? (If it makes no difference and could be changed, it will simplify your formula).

I'm in meetings most of Thursday so will be late afternoon before I can work on this. I do have the formula for your [Best Case Date] working now (for the "/" character). I made a copy of your sheet so I could work freely on it- you may remove me from your shared sheet if desired

Lisa Welch

Thank you Kelly,

The (10&15), (35&50), etc. are the days that would be calculated if that option was chosen in the Current Status dropdown off of the Current Status Updated date.

They can be changed as a description in the drop down, not an issue.

Appreciate the help!!

Lisa

Kelly Moore

It was the "&" I was asking about - the formula I'm using is locating the numbers in the text string by keying off of the special character "/" ,which is in all of your other responses. Inconsistently using special characters will result in needing multiple formulas to account for this. If it makes no difference to the user, then it would be easier if all of the dropdown responses that are being used in calculations follow a standard format.

Kelly