Overlapping Days - Overlapping Usage
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.
Task Name (who is using apartment and room) each name on an individual row
Check In Date
Check out Date
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.