Hello All,
I have two tables which I would like to parse through in order to eventually come up with 3 end result fields for each ID in Table 1: Start Date, End Date, and In CC.
I want: - Start Date to be looked up from Table2 where the max value less than the ActDate from Table 2 where Table 1 ID = Table 2 ID
- End Date to be the min End Date from Table 2 Greater than Act Date from Table 2 where Table 1 ID = Table 2 ID
- In CC should then return whether or not based on the Act Date from Table 1, whether or not the Act Date from Table 1 occurred PRE, DURING, or POST the Date Range for the Start Date and End Date from Table 2 where Table 1 ID = Table 2 ID
A few Caveats: - A record may not have any matching record in Table 2 for which Table 1 In CC should return Never in CC, and Start Date and End Date would be blank
- A record might have a Start Date and an End Date in Table 2 for which Table 1 would populate Start Date, End Date, and In CC as mentioned above
- A record might have a Start Date and no End Date in Table 2 for which Table 1 would populate Start Date, have End Date Blank, and In CC return DURING
As of right now I have a formula which successfully pulls the Start and End Dates for some, but in others returns essentially a fictitious Date for Start Date and End Date, and I am at a loss as for why. The fictitious date I get for the Start Date is 12/28/2015, and the fictitious date I get for End Date is 5/11/2016.
My formula for Start Date is:
=IF([@START]="CLOSED",
INDEX(Table2[START DATE], MATCH(MAX((Table2[CONCAT] = [@[ID]])*(Table2[START DATE] <= [@[Act Date]])*(Table2[CONCAT] = [@[ID]])*(Table2[END DATE] >= [@[Act Date]])),
(Table2[CONCAT] = [@[ID]])*(Table2[START DATE] <= [@[Act Date]])*(Table2[CONCAT] = [@[ID]])*(Table2[END DATE] >= [@[Act Date]]),0)),
IF([@START]="OPEN",
INDEX(Table2[START DATE], MATCH(MAX((Table2[CONCAT] = [@[ID]])*(Table2[START DATE] <= [@[Act Date]])),
(Table2[CONCAT] = [@[ID]])*(Table2[START DATE] <= [@[Act Date]]),0)),
"IDKYET"))
My formula for End Date is:
=IF([@START]="CLOSED",
INDEX(Table2[END DATE], MATCH(MAX((Table2[CONCAT] = [@[Full Name]])*(Table2[ID] <= [@[Act Date]])*(Table2[CONCAT] = [@[Full Name]])*(Table2[END DATE] >= [@[Act Date]])),
(Table2[CONCAT] = [@[Full Name]])*(Table2[ID] <= [@[Act Date]])*(Table2[CONCAT] = [@[Full Name]])*(Table2[END DATE] >= [@[Act Date]]),0)),
IF([@START]="OPEN",
INDEX(Table2[END DATE], MATCH(MAX((Table2[CONCAT] = [@[Full Name]])*(Table2[ID] <= [@[Act Date]])),
(Table2[CONCAT] = [@[Full Name]])*(Table2[ID] <= [@[Act Date]]),0)),
"IDKYET"))
Bookmarks