If I understand correctly, you want to enter a Collection Start Date and have the other dates calculated:
Business
|
D |
Development |
|
|
E |
Enhancement |
|
Kick Off Date |
|
4/1/2016 |
=(CollectionStartDate)-12 |
|
|
9/1/2016 |
=(CollectionStartDate)-7 |
Design Start |
|
5/1/2016 |
=(CollectionStartDate)-11 |
|
|
10/1/2016 |
=(CollectionStartDate)-6 |
QDRC Testing Date |
|
8/1/2016 |
=(CollectionStartDate)-8 |
|
|
NA |
NA |
UAT Start |
|
11/1/2016 |
=(CollectionStartDate)-5 |
|
|
12/1/2016 |
=(CollectionStartDate)-4 |
Collection Start Date |
|
4/1/2017 |
|
|
|
4/1/2017
|
|
I would use the DateAdd() function.
If you create a look up table "tblTimeLineMonths", you would end up with 80 records.
Table structure: (maybe)
SurveyList, Text ("90 Days Business", "90 Days Social", "Business", "IBSP, Social")
SurveyPart Text ("Kick Off Date", "Design Start", "QDRC Testing Date", "UAT Start")
Status, Text ("D", "E", "M", "MM")
MthOffset, Number - Integer (-12, -11, -8, -5, etc)
The syntax of the DateAdd function is
Code:
DateAdd(interval, number, date)
. To get the number you would have to use the DLookup function (or write a UDF) to get the interval/offset.
The syntax for DLookup is
Code:
DLookup(Expr, Domain, Criteria)
Putting them together, you get:
Code:
DateAdd("m", DLookup("MthOffset", "tblTimeLineMonths", "[SurveyList] = 'Business' AND [SurveyPart] = 'Kick Off Date' AND [Status] = 'D' ") , CollectionStartDate)
The question is "How are you going to use these dates?" Enter into a table? In a query? In an unbound form?