Overlapping Dates

[gslabosky]

I have a series of records for beginning and end dates that overlap, for example, for a guest's hotel stay. I want to eliminate overlaps in the records and keep a guest's earliest check in date and latest check out date for each stay. A check out followed by a check in on the same day would not count as an overlap. 

Here is an example set of records.

I would like this to become the following

Any suggestions?

[atcodedog05]

Hi @gslabosky 

Can you provide this sample data in excel?

[gslabosky]

Yep, here it is

[Christina_H · Accepted solution]

Try this.  It sorts by both dates, then uses a multi-row formula to take the highest end date if a row is part of the same stay as the previous row (forces ascending end dates for the same stay).  Then identify different stays in another multi-row formula, and finally group and take the min/max dates.

[atcodedog05]

Hi @gslabosky 

@Christina_H has done a great job on this usecase.

I was trying to solve it in a way different from the above posted solution but almost ended up with a similar approach. 😅 Hence i am not posting anything.

[gslabosky]

Thank you!