Formula for date change based on text in adjacent cell

[checklist]

Hello, and thanks in advance for the help.

Using Excel 2007, can anyone share the formula that will add one year to a date in a cell based on the text in an adjacent cell?

This is an Action Item list:
Cells in columns A - E are populated with the action descriptors.
Cells in Column F are populated with due dates. These cells already have four Conditional Formats that change the background color based on relative dates or absence of a date in a given cell. That is, a date greater than 120 days from today will color the cell green; if greater than one day the cell is yellow; and less than one day (yesterday or more), the cell is red. If the cell is blank, it is white.
Cells in column G all have a Drop Down List, preset with a single selection: COMPLETE. There is no other Conditional Formatting on Column G cells.

I would like to have a cell in Column F automatically add one year to the date in that cell, once COMPLETE is selected from the drop down list in the adjacent cell.

Thanks again,
checklist

[tlafferty]

Can you post a sample book?

[checklist]

Sorry, not sure what a sample book is, but I've attached the file. Hope that helps.

thanks,
checklist

[tlafferty]

Try this:

Please Login or Register  to view this content.

[checklist]

Thanks, not familiar with code (VBA?) use. Can that be converted into a formula and dropped in the formula bar or in the Conditional Formatting tool?

[tlafferty]

If we turn this into a formula, then all of your current static dates will have to be entered into the formula. In other words, you'd have to retype them all just to have them automated when a user clicks COMPLETE in the adjacent cell.

If you'd really like to do that, this will do the trick:

Please Login or Register  to view this content.

Two things to pay attention to:
1) change B1 to whatever cell the word COMPLETE will appear in, and enter the formula in the cell where your static date appears.
2) Each of the current dates will have to be hand coded in.

If you'd rather let VBA take care of this for you:
1) Open the workbook to be automated
2) Press Alt + F11 on your keyboard to open the Visual Basic editor
3) Click the Insert menu and select Module
4) Paste this code into the module:

Please Login or Register  to view this content.