Assigning a Date to a Date Period

[zpenacho]

I have individual dates that I need to assign to a 14 day period if they fall within that 14 day period.

I've attached a workbook example and detailed the formula's outcome below.

For example, if my date is 12/25/2011, I need the cell next to it to read 1/7/2012. I would need all of the dates 12/25/2011 - 1/7/2012 to read 1/7/2012 in the column adjacent. 1/8/2012 would need to read 1/21/2012 in the column adjacent as well as all the dates 1/8/2012 - 1/21/2012 read 1/21/2012 in the column adjacent. I need the formula to work into perpetuity.

Any ideas? Any help is appreciated, thanks!

[daddylonglegs]

Try this formula in B2 copied down

=A2-MOD(A2-DATE(2011,12,25),14)+14

that will work for any date

[zpenacho]

Had to change it to +13 at the end, but other than that, perfect! Thanks longlegs!!