Counting total duration of stay with multiple entries per customer

Question

I have an excel file that has 2 spreadsheets. First spreadsheet ("masterlist") contains the masterlist with 4 columns:

CustomerID	Checkin	Checkout	Duration
1	9/1/2020	9/3/2020

A customer might have multiple entries as they could've checked in and out multiple times. There's thousands of records/rows overall.

The 2nd spreadsheet ("Infosheet") just has ONLY the unique customers from masterlist and is structured:

CustomerID	Total_Duration
1

My end goal is to calculate the total duration of each customer's stay.

For calculating Duration in "masterlist," I did a simple if statement formula:

=IF(checkout=checkin,1,checkout-checkin+1)

If statement, because if the checkin and checkout are on same day, the customer is still considered to have stayed 1 calendar day. The addition of 1 in the false case is done to account for the same full calendar day. So a stay of 9/1-9/3 should yield 3 days total.

Long story short, I ended up with a formula that reads the following for the "Infosheet"'s Total_Duration:

=SUMIF(masterlist!$A:$A,A2,masterlist!$D:$D)

Problem is that there's multiple assumptions made using my logic that ends up getting multiple records wrong. If a customer has the following two records:

Checkin	Checkout	Duration
9/1/2020	9/3/2020	3
9/3/2020	9/5/2020	3

My formulas calculate the duration of stays for both as 3 days and in the "Infosheet" the total duration would show as 6 days. The correct result would have been 5 days, because we shouldn't be counting 9/3/2020 twice.

My formula also doesn't account for a case like the one below:

Checkin	Checkout	Duration
9/1/2020	9/3/2020	3
9/3/2020	9/3/2020	1
10/1/2020	10/3/2020	3
10/3/2020	10/3/2020	1

While the duration, in isolation, are calculated correctly, the SUMIF would give us a total duration of 8 days. In reality, the 9/3-9/3 and 10/3-10/3 stays have already been accounted for in the 9/1-9/3 and 10/1-10/3 stays so it shouldn't have been counted again and the total duration should've been 6 days.

I am completely stumped on what should be my next step. How do I account for these examples in my formula? How should I be manipulating the data/changing or adding columns to make this easier?

Thanks in advance!

Please have a look at the good answers to https://stackoverflow.com/questions/68525046/i-have-3-time-periods-in-excel-i-need-to-know-the-duration-of-the-longest-cont — Qualia Communications, Aug 02 '21 at 18:01
Just for interest, what is the scenario where a customer checks in and out on the same day? I would understand it better if their original stay was 9/1/2020 to 9/3/2020 (2 nights) then they added 9/3/2020 to 9/4/2020 and extended their stay by one night. Maybe if you based your calculations on number of nights it would be easier? — Tom Sharpe, Aug 02 '21 at 20:42

Scott Craner · Accepted Answer · 2021-08-02T19:25:05.327

4

we can use Sequence to create an array of days from the Min to the Max dates and use COUNTIFS to find if they fall inside the ranges.

=LET(
    ID,A:A,
    ckin,B:B,
    ckot,C:C,
    ids,F2,
    mn,MINIFS(ckin,ID,ids),
    mx,MAXIFS(ckot,ID,ids),
    sq,SEQUENCE(mx-mn+1,,mn),
    SUMPRODUCT(--(COUNTIFS(ckin,"<="&sq,ckot,">="&sq,ID,ids)>0))

edited Aug 02 '21 at 19:25

answered Aug 02 '21 at 19:05

Scott Craner

148,073
10
49
81

2

Nicely done. I was going to provide a PQ solution but yours looks fine. – Ron Rosenfeld Aug 02 '21 at 19:18
@RonRosenfeld But that has been our template. I write this long intricate formula and you come in and take the check mark with a PQ answer. I do not know how to react. :) – Scott Craner Aug 02 '21 at 19:21
Alternatively you could amend the formula in the master list `=C2-B2+1-ISNUMBER(MATCH(1,($C$1:C1>=B2)*($A$1:A1=A2),0))` and drag down. But I love your solution. – P.b Aug 02 '21 at 19:22
1

I just added a line to the table: `2 8/15/2020 10/15/2020 62`. The customer days for #1 increased to 33. – Ron Rosenfeld Aug 02 '21 at 19:23
1

@RonRosenfeld see edit. I forgot to add the condition to the countifs. Thanks. – Scott Craner Aug 02 '21 at 19:25
I suppose there _should_ in theory only ever be an overlap of 1 day on re-booking to extend a stay so you could simplify to allow for this, but it's good to have a general formula that doesn't depend on this assumption. – Tom Sharpe Aug 02 '21 at 20:34

Counting total duration of stay with multiple entries per customer

1 Answers1