How can I add days to dates based on text in another column?

Options

I need to have a specific number of days added to a date that is in Column A, and have it add days to Column C, based on the text that is in Column B. Example:

Column A is SC (needs +20)

Column B is 7/29/2020

Column C should = 8/18/2020

I have found that (A1,"SC",B1)+20 works per text, but I have 12 different variances of this formula, all with different days that would need to be added based on specific text in Column A. Any help would be greatly appreciated, I just cannot get the formula to be specific based on the text.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You COULD use a nested IF formula to account for every difference in Column A, but updates/additions/removals would be very cumbersome.


    I would suggest creating a table that has each of the Column A variables listed out in one column and the corresponding number of days listed in another column.


    Then you could use something along the lines of...

    =[Column B]@row + INDEX([Table Number Column]:[Table Number Column], MATCH([Column A]@row, [Table Variable Column]:[Table Variable Column], 0))


    This will look for the value in Column A within the table and pull the appropriate number to be added to the date.


    Working with a table like this means that any time you need to add, remove, or change a variable or number of days, you only need to update the table which (in my opinion) is much easier to manage than trying to manage a long formula and then updating all instances of said formula on the sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!