How to calculate days elapsed since initial record on a per unique record basis

Hi again... couldn't quite find the answer for what I was looking for so hopefully my explaination will help.

My goal is to find unique records of patients who have a 6 month interval of treatment within the database (we want to find people with 6 months weight loss and then see if it is maintained over the subsequent time in the clinic). It doesn't matter when the interval starts and the the most inclusive set of patients under the 6 month criteria would be patients whose difference between the first visit and most recent visit total > 180 days.

I have been able to find these patients relatively easily by using =IF(A3=A2,C3-C2,0) where A column contains the unique patient ID and the C column contains the dates of the visits. However, this only gives me the list of patients who have at least a 6 month interval of time from their first visit to their most recent.

Is there a formula to calculate the days elapsed since the initial visit for each unique patient's record? With this, I can figure out which records are associated to the interval and then find the records for maintenance.

Please see example spreadsheet: example.xlsx
The text in RED is what the output should look like.
PID_ENCID is the unique patientID and EncounterID (1735.04) would signify patient 1735's 4th visit.
I would like to know if there is a formula that would calculate the difference in days against each patient's initial visit. Thus, for patient 1735, the formula would calculate the difference between records 1735.02 - 1735.01, then 1735.03 - 1735.01, and finally 1735.04 - 1735.01.

Let me know if I can provide any more information. I have a feeling this is going to deal with sumproduct formulas.

Originally Posted by benishiryo

hi JJ22. if i'm right & G23 should show 70, try this in G2 & drag down:
=C2-SUMPRODUCT((INT($B$2:$B$34)=INT(B2))*($B$2:$B$34=INT(B2)+0.01)*$C$2:$C$34)

I have never been more impressed by a forum. I have submitted so many questions that have erroneous formulas that are caught by the readers (yes, G23 should be 70). I mixed up the formula when I made the example and used =if(A3=A2,C3-C2,0) instead of =if(A2=A1,C2-C1,0). Even my output that I wanted was incorrect and your formula was correct.

Superb answer and just what I was looking for!

In situations where the initial unique record ID is not consistent (I had initally explained the records start XX.01 and so on), but in my real database, I had instances where records would initially start as XX.02, the formula to remedy this would be:
=C2-SUMPRODUCT((INT($B$2:$B$4286)=INT(B2))*($B$2:$B$4286=INDIRECT("B"&MATCH(A2,$A$1:$A$4286,0)))*$C$2:$C$4286)

See example2.xlsx in post #4 to see how the old formula did not work, but the new one will.

Solved!!