Overlapping Days - Overlapping Usage

GarryD2

I searched and found things close but not quite and can't quite get it to work.

I have two apartments 1 & 2 and each apartment has room A and B. Using a standard project template we enter the person's name using the apartment in the Task column. We have one dropdown to select apartment 1 or 2 and second drop down column to select A Side or B Side. Then a helper column that combines them to say 1 A Side or 1 B Side. Then Check In Date, Check Out Date, Duration. I calculate the number of days each room is used individually (doing it simple with helpers). I have an apartment 1 usage days column and simply if Combined = 1 A SIDE then put Duration in that column. Same for B, same for 2 A and 2 B. Then in sheet summary I do a summary of that column to get the number of days each apartment 1 A side Usage Days, 1 B side Usage A side and B side was used. Now I need to know how many days have both the A and B side for apartment 1 been simultaneously occupied. Same for apartment 2. Then lastly when have 1 A & B and 2 A & B all been simultaneously occupied.

Columns:

Task Name (who is using apartment and room) each name on an individual row

Check In Date

Check out Date

Duration (Days)

Apartment (1 or 2) (can only select one or the other multiple select off)

Side (A Side or B Side) (multiple select off, each side would be a separate row)

Combined = apartment # X Side 1 A Side or 1 B Side; 2 A Side or 2 B Side

Looking how to calculate simultaneous usage of a single apartment both sides

And simultaneous usage of both apartments both sides (or all four available spots)

Thanks in advance for help.

Genevieve P.

Hi @GarryD2

I can see where you're getting stuck, this is a complicated one! 🙂

Personally, the way I would deal with this is I'd set up an entire second sheet. In this sheet I would list down every day of the year as an individual date.

That way I can use a formula to check the original intake sheet based on each day and return what Apartment(s) were booked on that one day.

So, say my intake sheet looks like this:

My second sheet would look like this:

The formula I used is as follows:

=JOIN(COLLECT({Apartment}, {Start Date}, <=[Date (Day)]@row, {End Date}, >=[Date (Day)]@row), CHAR(10))

I input this in a Multi Select column, using CHAR(10) as the delimiter in the JOIN(COLLECt formula to return multiple selections.

Now that I have each day individually identified with the booked apartments, we can create your next calculations. This could either be done here, in the second sheet, or in the Sheet Summary fields of your first sheet (using cross-sheet formulas).

For example, to see how many days "1 A Side" and "1 B Side" are selected on the same day, I would use this COUNTIFS formula:

=COUNTIFS({Apartments Booked}, AND(HAS(@cell, "1 A Side"), HAS(@cell, "1 B Side")))

You can adjust this for any combination you need:

=COUNTIFS({Apartments Booked}, AND(HAS(@cell, "1 A Side"), HAS(@cell, "1 B Side"), HAS(@cell, "2 A Side"), HAS(@cell, "2 B Side")))

Let me know if this makes sense and will work for you!

Cheers,

Genevieve