EX:

Launch date = 2/16/2014

List for someone to select = T+1. T-0, T-1 etc...

T+1 = 7 days after the launch, T-1 = 7 days before the launch etc...If want a formula that will take the list selection (T-1) and then count back by 7 days from the launch date to populate a due date for that action item.

When you say "List for someone to select = T+1. T-0, T-1 etc..."do you mean a Data Validation Drop Down List?If so, something like this might work.

Column A contains the values for your Drop Down List

Column B contains your launch date

Column C contains the actual Drop Down

Column D contains the formula shownFor T-2, the formula will return 2/2/2014.

A B C D 1 T-2 2/16/2014 T-2 =RIGHT(C1,LEN(C1)-1)*7+B1 2 T-1 3 T+0 4 T+1 5 T+2Note, if the "T" values will always be a single digit, then you can use this:

=RIGHT(C1,2)*7+B1

Using the LEN function as above allows for any value, such as 20, 137, etc., as long as the value doesn't result in an invalid date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

OMG, that's amazing! Thank you so much. Maybe you can help me with the next part...

One page one I do have a list to select from (T+4, T+3, T+2 etc...) and the next column over is a due date. I think I need an IF statement so that if I select T-2 from the list, the due date will auto populate to sheet 2, D1 (D1 being from your example above. Does that make sense?

A B

1. T-2 (from list) 2/2/2014 (sheet 2, d1 (formula you provided))

2. T+4 (from list) 3/16/2014 (sheet 2, formula you provided)

message edited by tabitha8503

First, a posting tip: Before posting example data in this forum, please click on the

blue lineat the end of this post and read the instructions on how to format example data so that it is easier for us to read. Thanks!Is this what you are looking for?

=IF(Sheet1!C1="","",RIGHT(Sheet1!C1,2)*7+Sheet1!B1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History