DateDiff of Row2 "From Date" to Row1 "To Date" when IDs Match

[cpbrehmer]

Hi Everyone,

Trying to figure out how to use the Formula tool to find every record where a Date Diff results in a value greater than 1. The challenge I am having is I am not sure how to get the formula to work by doing the Date Diff on Row2 to Row1

ID	Record	From Date	To Date
1234	1	12/31/2022	3/31/2023
1234	2	4/1/2023	6/30/2023
1234	3	8/15/2023	null
5678	1	6/15/2023	7/25/2023
5678	2	8/4/2023	null

I would like to do the Date Diff on Record 2 to Record 1 or Record 3 to Record 2, when the IDs match and get the results in Days.

[Qiu · Accepted solution]

@cpbrehmer 
It is not clear to me that how you want to calculate the Date Diff, it is between From Date and To Date? Or The "To Date" between Row 2 and Row 1 or other case?

I assume The "To Date" between Row 2 and Row 1 and we can use the MultiRow Formula tool as below.

[cpbrehmer]

@Qiu 
My apologies for not being clearer in the original question. The ask is to calculate the Date Diff between the From Date of Row 2 and the To Date of Row 1. A valid data condition is for the From Date of Row 2 to be only 1 day after the To Date of Row 1. Anything larger than 1 day means I have a gap that needs to be corrected.

[cpbrehmer]

@Qiu 

I think your solution works even though I wasn't clear in my original question. By making sure my records are ordered properly, I was able to use the Multi-Row Formula tool with this formula:

DateTimeDiff([From Date],[Row-1:To Date],"Days")

This appears to give me what I was looking for.

Thanks!

[Qiu]

@cpbrehmer 
Glad it gets sorted out. 😁