Formula Help - Calculate Occurrences between two times

[leighjones123]

Hi all. Looking for some help with a formula to do what I need... For whatever reason, I can't seem to find the right COUNTIFS.

In ColA I have a start time (for a booking)
In ColB is the end time

I'm trying to create a table which shows me how many bookings are active within the times set.

My data example...

Start Time	End Time	Booking Name
12:00​	14:00​	Mr A Smith
12:30​	14:30​	Mr B Smith
13:00​	15:00​	Mr C Smith
16:00​	18:00​	Mr D Smith

My required table (with the end result)...

Start Time	End Time	Bookings Active
12:00​	12:29​	1​
12:30​	12:59​	2​
13:00​	13:29​	3​
13:30​	13:59​	3​
14:00​	14:29​	2​
14:30​	14:59​	1​
15:00​	15:29​	0​
15:30​	15:59​	0​
16:00​	16:29​	1​
16:30​	16:59​	1​
17:00​	17:29​	1​
17:30​	17:59​	1​

Just to add context... this is to help me to see how many tables are in use within my restaurant at what times... so I can effectively plan staffing.
Unfortunately, my booking system doesn't automatically give me the data I need.

I've been able to work out how to find out how many tables/customers are arriving within a particular time window... but this doesn't tell me exactly how many total customers I've got in the restaurant within a particular time window.

Hope anyone can help

Best
Leigh

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

However, see if this is what you want.

20 12 13.xlsm
	A	B	C
1	Start Time	End Time	Booking Name
2	12:00	14:00	Mr A Smith
3	12:30	14:30	Mr B Smith
4	13:00	15:00	Mr C Smith
5	16:00	18:00	Mr D Smith
6
7
8	Start Time	End Time	Bookings Active
9	12:00	12:29	1
10	12:30	12:59	2
11	13:00	13:29	3
12	13:30	13:59	3
13	14:00	14:29	2
14	14:30	14:59	1
15	15:00	15:29	0
16	15:30	15:59	0
17	16:00	16:29	1
18	16:30	16:59	1
19	17:00	17:29	1
20	17:30	17:59	1
Bookings

Cell Formulas
Range	Formula
C9:C20	C9	=COUNTIFS(B$2:B$5,">"&A9,A$2:A$5,"<"&B9)

 

[leighjones123]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

However, see if this is what you want.

20 12 13.xlsm
	A	B	C
1	Start Time	End Time	Booking Name
2	12:00	14:00	Mr A Smith
3	12:30	14:30	Mr B Smith
4	13:00	15:00	Mr C Smith
5	16:00	18:00	Mr D Smith
6
7
8	Start Time	End Time	Bookings Active
9	12:00	12:29	1
10	12:30	12:59	2
11	13:00	13:29	3
12	13:30	13:59	3
13	14:00	14:29	2
14	14:30	14:59	1
15	15:00	15:29	0
16	15:30	15:59	0
17	16:00	16:29	1
18	16:30	16:59	1
19	17:00	17:29	1
20	17:30	17:59	1
Bookings

Cell Formulas
Range	Formula
C9:C20	C9	=COUNTIFS(B$2:B$5,">"&A9,A$2:A$5,"<"&B9)

thanks so much Peter, this seems to work great. Thanks for the tip re updating my account information - I'll do that

 

[leighjones123]

Peter,

Could I ask a follow-on question?

So this works perfectly for the count of how many bookings (tables) I'll have in use within any given time window. I adapted the ranges to fit my acutal data (I simplified it above for ease).

In my data, I've also got a Column which shows how many people are within that booking (customers).
To get this information into my table, I figured I could use a SUMIFS instead of a COUNTIFS - but it doesn't seem to work as expected. At the earlier time slots, it's giving me the total people for the day.

Any ideas how I could get around this?

Best,
Leigh

What I tried:

=SUMIFS(SUN!$G$2:$G$78,SUN!$AA$2:$AA$78,">"&W6,SUN!$E$2:$E$78,"<"&X6)

 

[Peter_SSs]

thanks so much Peter, this seems to work great.

Good news!

Thanks for the tip re updating my account information - I'll do that

Thanks for updating your profile.

Now for the next tip: I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. For example, I cannot see what columns/rows the info in post #4 is in. Nor could I copy it if I wanted to use for testing.

Try the SUMIFS given in column D below. Also, now that we know you have Excel 365, alternative suggestions for both results are given in columns E:F. See how they go.

20 12 13.xlsm
	A	B	C	D	E	F
1	Start Time	End Time	Booking Name	Customers
2	12:00	14:00	Mr A Smith	4
3	12:30	14:30	Mr B Smith	10
4	13:00	15:00	Mr C Smith	5
5	16:00	18:00	Mr D Smith	13
6
7
8	Start Time	End Time	Tables	Customers	Tables	Customers
9	12:00	12:29	1	4	1	4
10	12:30	12:59	2	14	2	14
11	13:00	13:29	3	19	3	19
12	13:30	13:59	3	19	3	19
13	14:00	14:29	2	15	2	15
14	14:30	14:59	1	5	1	5
15	15:00	15:29	0	0	0	0
16	15:30	15:59	0	0	0	0
17	16:00	16:29	1	13	1	13
18	16:30	16:59	1	13	1	13
19	17:00	17:29	1	13	1	13
20	17:30	17:59	1	13	1	13
Bookings

Cell Formulas
Range	Formula
C9:C20	C9	=COUNTIFS(B$2:B$5,">"&A9,A$2:A$5,"<"&B9)
D9:D20	D9	=SUMIFS(D$2:D$5,B$2:B$5,">"&A9,A$2:A$5,"<"&B9)
E9:E20	E9	=COUNT(FILTER(D$2:D$5,(B$2:B$5>A9)*(A$2:A$5<B9)))
F9:F20	F9	=SUM(FILTER(D$2:D$5,(B$2:B$5>A9)*(A$2:A$5<B9),0))

 

[leighjones123]

Thank you again Peter, that works great. Thanks for the tip re XL2BB also, I'll take a look

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.