Comparing dates in Excel

Question

Hi,

I have three columns in my Excel sheet: customer_id, entry_date and leave_date. I want to check whether the same patient has rentered within 14 days after leaving last time. In other words, i want excel to check in a separate column if, for a given customer_id, two or more records exist, where the entry_date in one is no more than 14 days later from leave_date in another. In the column that will run this function, i want 1 to be returned when such a case occurred, and 0 otherwise.

Is that possible to do in Excel. If yes, how?

Answer 1

Is the data sorted by id and entry date? If the leave date is in row 5, will the next entry date, if it exists, always be in row 6?

If so then the following formula should do what you want:

=IF(AND(current_id=next_id,next_entry_date-current_leave_date<=14),1,0)

If not, then please provide additional details on how the multiple entries are related.