How to count for between two dates in 2 colums with different row in Excel

Question

I have a spreadsheet with 2 columns with dates and one with a Room #:

Date In	Date Out	Room No
2/3/2023	2/5/2023	Room 1
2/5/2023	2/8/2023	Room 1
2/8/2023	2/10/2023	Room 1
3/23/2023	3/26/2023	Room 1
3/30/2023	3/31/2023	Room 5
4/6/2023	4/8/2023	Room 6
4/13/2023	4/14/2023	Room 1
4/19/2023	4/21/2023	Room 2
4/23/2023	4/26/2023	Room 3

I want to count how many date out - next date in for the same Room # = 0 or more than 1.

Is there any way of doing this?

Any suggestions are gratefully accepted. thanks

Example using match

Answer 1

You may try these formula:

---

Formula in H27: =SUM(IF(C26:C34=F25, IF(ISNUMBER(MATCH(B26:B34, A26:A34, 0)), 1, 0)))

Formula in H28: =SUM(IF(C26:C34=F25, IF(ISNUMBER(MATCH(B26:B34, A26:A34, 0)), 0, 1)))

---

If you are using higher version of Excel like 2021 or 365 then you may try this also.

H27: =COUNT(FILTER(B26:B34, (C26:C34=F25)*(ISNUMBER(MATCH(B26:B34, A26:A34, 0))=TRUE)))

H28: =COUNT(FILTER(B26:B34, (C26:C34=F25)*(ISNUMBER(MATCH(B26:B34, A26:A34, 0))=FALSE)))

---

N.B.

• Cell F25 has Criteria, and is editable better create a Drop Down.
• Adjust cell references as needed.

Answer 2

Use =DAYS(end,start) Formula In C1, A1 is start date and B1 is end date =DAYS(B1,A1)

Answer 3

I only noticed now that you tagged Excel 2007:

You may want to use this for that version: =SUM(--ISNUMBER(MATCH(B2:B10&F3,A2:A10&F3,0))) =SUM(ISNA(MATCH(B2:B10&C2:C10,A2:A10&C2:C10,0))*(C2:C10=F2))-1

!! Note that both formulas require being entered with ctrl+shift+enter

The first returns the number of results where the Room in column C equals the value in F3 where the Date Out value is also found in the Date In column where the room equals F3.

The second returns the number where the Date In value doesn't equal the Date out value doesn't equal where the room equals F3. But since there's always a last value that doesn't have a later date; we subtract 1 of the summed total.

If you use Office 365 you could achieve the following:

Maybe my answer is not in the format you requested, but I think this may be giving you more insight:

=LET(sorted,SORT(A2:C10,{3,2}),
     room,TAKE(sorted,,-1),
     roomcomp,(DROP(room,-1)=DROP(room,1)),
     indate,DROP(TAKE(sorted,,1),1),
     outdate,DROP(INDEX(sorted,,2),-1),
VSTACK(HSTACK(A1:C1,
              "next date in "&{"=",">"}&" previous date out"),
       IFERROR(HSTACK(sorted,
                      (roomcomp)*(outdate=indate),
                      (roomcomp)*(outdate<indate)),
               "")))

It will show the same range, but sorted by Room, then date out and the result by row for the next indate being equal to or greater than the previous outdate. That way you also see for which date(s) the conditions apply.

Basically what it does is =(B2:B9=A3:A10)*(C2:C9=C3:C10) and =(B2:B9<A3:A10)*(C2:C9=C3:C10) but on the Range B2:C10 sorted by Room then by Date (out).

Another way of creating more insight is to show the last day out where the day in and day out are consecutive for the same room:

=LET(a,A2:A10,b,B2:B10,c,C2:C10,r,SEQUENCE(ROWS(a)),MAP(a,b,c,LAMBDA(u,v,w,REDUCE(v,r,LAMBDA(x,y,XLOOKUP(x&w,a&w,b,x))))))