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

Sean Dawson

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.

Paul Newcome

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.