Problem description & analysis

Excel records dates when events under different users happen. The data is ordered by USER ID and DATE. Below is part of the file:

We want to add a calculated column isTrue according to a specific rule. For the same USER ID, the first event is always true; from the second event on, if the interval between this event and the last true event is 10 or more days, the event is true, otherwise it is false. The expected result should be as follows:

Solution

Use the SPL XLL plug-in of Excel

Write the formular in cell D1:

=spl("=E(?).new(if('USER ID'!='USER ID'[-1],(t=DATE,1),if(DATE-t>=10,(t=DATE,1),0)):isTrue)",A1:C7)

As shown:

Explanation:

If USER ID in the current row is different from that in the last row, the current event is the first one in its group. In this case, assign 1 to isTrue and the temporary value t is equal to the current DATE. If USER ID in the current row is the same as that in the last row, an intra-group calculation is needed, which uses the temporary variable and the current DATE to calculate the interval and assign 1 to isTrue if the result is equal to or greater than 10, and modify the value of temporary variable t to the current DATE.

Q & A Collection

https://stackoverflow.com/questions/63838362/date-diff-between-many-rows-in-excel