My data looks like: ID Start_day End_day 1 - 5/1/20 - 5/31/20 1 - 6/6/20 - 7/5/20 1 - 7/8/20 - 8/7/20 1 - 9/9/20-10/8/20 ... I am trying to first calculate the gap between a person's last end day and the next start day. e.g. for person 1, the gap should be 6/6/20 - 5/31/30 =6 days (which I already calculated for each person successfully). Then, I want to calculate the duration for a person if he/she has no gap > 30 days. e.g. person 1 has gap > 30 days at the 4th row, so the duration should be 8/7/20 minus 5/1/20 (99 days). Plus, a person could have multiple durations if he/she has more than one gap (>30 days). e.g. A person could have first duration on 5/1/10 - 5/1/11. Then, he/she have a gap of, for example, 180 days and he/she start another period 11/1/2011 - 1/31/11 and then they could have another gap of > 30 days. Then they could start a third period. The duration calculation is what I am struggling with. Any suggestion? Thanks
... View more