I have an Excel sheet in long format where each observation is in a separate row. Some patients have 1 observation, some have many. I know the date of each observation. I am trying to label the first observation for each patient as Day 0 and any subsequent observations for that patient as the number of days from their baseline visit to the current visit. Below is some mock data demonstrating the data format:

Patient ID  Date
101 9/22/17
101 10/6/17
102 8/3/16
102 8/25/16
102 1/26/17
102 3/22/18
102 5/3/18
104 10/26/15
104 11/18/15
104 6/14/16
104 7/20/16
104 8/8/16

Any simple solutions for this? Thank you, LM

  • You tried to post an answer saying that SHawarden's solution worked for you. The way to indicate that is to accept the answer by clicking the checkmark next to it. That will alert readers with a similar problem that there's a proven solution here. It will also award a little rep to both of you for the effort. – fixer1234 May 15 '19 at 16:56

Assuming that matching patient IDs are grouped together and any new entries are inserted at the bottom of their current list of entries, pushing lower patients down by one row.

C2: =IF(A2=A1,B2-B1+C1,0)

If this row's patient id cell matches the previous row's patient id cell:

  • Subtract the previous row's date formatted cell from this row's date formatted cell
  • Add the previous count.

Otherwise, if the previous row's id cell doesn't match the current one:

  • It's a new patient on day 0.

Then just drag the cell down to auto-populate the proceeding cells with the correct references.

  • Sorted. Thx @fixer1234 – SHawarden May 14 '19 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.