Update values based on date in another row

[jrdnjhnsn2]

I've got some historical policy data, and I need to fix a couple of errors in it. 

The part I'm struggling with is what to do with records 39 and 40. We're going to treat the date the change from coverage_status 1 to coverage_status 4 happened as 6/29/2009, which can be found in row 41 in the transaction_date column. I want to have have a period_start and period_end column for the records in rows 39 and 40. For record 39, I want the period_start to be 4/1/2009, and the period end to be 6/28/2009, and for record 40, I want the period_start to be 6/29/2009, and the period_end to be 6/30/2009.

Since these dates are in different rows, I'm having a hard time getting this to work systematically for different scenarios. 

I've attached my starting point, and my desired output is included as well.

Any ideas how this can be done?

[MattBSlalom · Accepted solution]

You're looking for the Multi-Row Formula tool.  In this sample, I had to convert a couple of your fields to an actual Date datatype (hence the field names with the "2" suffix), then have added 2 Multi-Row Formula tools to generate the Start & End dates.  You didn't specify where the April 1st value came from for row 39, so I just assumed the Min_PU_Date; this can easily be swapped with the effective_date if that was the desired source field.

Sample workflow:

The formula for the Period Start date is:

IF [New_Coverage_Status] != [Row+1:New_Coverage_Status]
THEN [Min_PU_Date2]
ELSEIF [New_Coverage_Status] != [Row-1:New_Coverage_Status]
THEN [Row+1:transaction_date2]
ELSE Null()
ENDIF

The formula for the Period End date is:

IF [New_Coverage_Status] != [Row+1:New_Coverage_Status]
THEN DateTimeAdd([Row+2:transaction_date2], -1, "day")
ELSEIF [New_Coverage_Status] != [Row-1:New_Coverage_Status]
THEN DateTimeAdd([Row+1:transaction_date2], 1, "day")
ELSE Null()
ENDIF