Formula to count entries that fall on a specific day and time range

[meronymous]

Hello everyone,

I'm hoping someone can help me come up with a formula to count the number of entries that fall on a specific day of the week and within a specific time range. I have a long table of data in Excel that includes two columns: "Entry Time" and "Exit Time". These columns are formatted as "dd.mm.yyyy HH:mm", and the time between the two can span over several days.

What I need is a formula that checks if the period between Entry Time and Exit Time both fall on a specific day of the week (e.g., Wednesday) and within a specific time range (e.g., between 8:00 AM and 10:00 AM). If the conditions are met, I want to add 1 to the count.
I have no problem doing this when B and C are on the same date, but I struggle to find a way to make Excel count as + 1 all the hours and days in between when the difference between B and C goes over midnight.

Here's a screenshot of my table:

If anyone could help me put this together, I'd be very grateful!

Thanks in advance.

 

[AlanY]

is this what you're after?

Book1
	A	B	C	D	E	F
1					Between
2	Enter	Exit		Weekday	10:00	12:00
3	04/04/2023 10:10	04/04/2023 11:10		1	FALSE
4	04/04/2023 10:10	04/04/2023 11:10		2	FALSE
5	04/04/2023 10:10	04/04/2023 11:10		3	TRUE	1
6	04/04/2023 09:10	04/04/2023 10:10		3	FALSE
7	04/04/2023 10:10	04/04/2023 15:10		3	FALSE
8	04/04/2023 10:10	04/04/2023 11:10		6	FALSE
9	04/04/2023 10:10	04/04/2023 11:10		7	FALSE
Sheet1

Cell Formulas
Range	Formula
E3:E9	E3	=AND(WEEKDAY(A3)=D3,WEEKDAY(B3)=D3,AND(A3-INT(A3)>=E$2,B3-INT(B3)<=F$2))
F3:F9	F3	=IF(E3,1,"")

 

[meronymous]

Close, but not exactly that.
I want the table on the right to go only from 1 to 7 (the seven days in a week).
In E5 the correct result would be 6, meaning in column A:B there's 6 occurrences being both on day 3 and between 10:00 and 11:00.

I've managed to do this already with some slightly different formulas, but I can't make it work when the value in A:B (in your example) are something like 02/04/2023 10:00 and 05/04/2023 12:00.
This pair of values should as well be considered as +1 in cell E5, putting the end result to 7.

 

[AlanY]

ok, try this

Book1
	A	B	C	D	E	F
1					Between
2	Enter	Exit		Weekday	10:00	12:00
3	04/04/2023 10:10	04/04/2023 11:10		1	0
4	04/04/2023 10:10	04/04/2023 11:10		2	0
5	04/04/2023 10:10	04/04/2023 11:10		3	5
6	04/04/2023 09:10	04/04/2023 10:10		4	0
7	04/04/2023 10:10	04/04/2023 15:10		5	2
8	04/04/2023 10:10	04/04/2023 11:10		6	0
9	04/04/2023 10:10	04/04/2023 11:10		7	0
10	06/04/2023 10:10	06/04/2023 11:10
11	06/04/2023 10:10	06/04/2023 11:10
12	06/04/2023 09:10	06/04/2023 10:10
13	06/04/2023 10:10	06/04/2023 15:10
14
Sheet1

Cell Formulas
Range	Formula
E3:E9	E3	=SUMPRODUCT((($A$3:$A$13)-INT($B$3:$B$13)>=E$2)*(($B$3:$B$13)-INT($B$3:$B$13)<=F$2)*(WEEKDAY($A$3:$A$13)=D3)*(WEEKDAY($B$3:$B$13)=D3))

 

[AlanY]

Close, but not exactly that.
I want the table on the right to go only from 1 to 7 (the seven days in a week).
In E5 the correct result would be 6, meaning in column A:B there's 6 occurrences being both on day 3 and between 10:00 and 11:00.

I've managed to do this already with some slightly different formulas, but I can't make it work when the value in A:B (in your example) are something like 02/04/2023 10:00 and 05/04/2023 12:00.
This pair of values should as well be considered as +1 in cell E5, putting the end result to 7.

sorry, just read that

02/04/2023 10:00 and 05/04/2023 12:00. This pair of values should as well be considered as +1 in cell E5, putting the end result to 7.

but they are not on the same day?

 

[meronymous]

sorry, just read that

but they are not on the same day?

Well,
02/04/2023 10:00 and 05/04/2023 12:00 should get a +1 generated for every hour cell on day 1 (from 10), on every single hour on day 2, 3, and finally start again giving 0 after 12 on day 4 (05/04/23 12:00).
This while taking in account all the other values in columns A:B.

 

[AlanY]

have a look if this works for you, col E is entry between the set time and col F is exit. G is the maximun of the 2.

Book1
	A	B	C	D	E	F	G
1					Between
2	Enter	Exit		Weekday	09:00	12:30
3	04/04/2023 10:10	04/04/2023 11:10		1	0	0	0
4	04/04/2023 10:10	04/04/2023 11:10		2	0	0	0
5	04/04/2023 10:10	04/04/2023 11:10		3	7	5	7
6	04/04/2023 09:10	04/04/2023 10:10		4	1	0	1
7	04/04/2023 10:10	04/04/2023 15:10		5	3	5	5
8	04/04/2023 10:10	04/04/2023 11:10		6	0	0	0
9	04/04/2023 10:10	06/04/2023 11:10		7	0	0	0
10	06/04/2023 10:10	06/04/2023 11:10
11	06/04/2023 10:10	06/04/2023 11:10
12	06/04/2023 09:10	06/04/2023 10:10
13	05/04/2023 10:10	06/04/2023 11:10
Sheet2

Cell Formulas
Range	Formula
E3:E9	E3	=SUMPRODUCT((($A$3:$A$13)-INT($A$3:$A$13)>=E$2)*(WEEKDAY($A$3:$A$13)=D3))
F3:F9	F3	=SUMPRODUCT((($B$3:$B$13)-INT($B$3:$B$13)<=F$2)*(WEEKDAY($B$3:$B$13)=D3))
G3:G9	G3	=MAX(E3:F3)

 

[meronymous]

have a look if this works for you, col E is entry between the set time and col F is exit. G is the maximun of the 2.

Book1
	A	B	C	D	E	F	G
1					Between
2	Enter	Exit		Weekday	09:00	12:30
3	04/04/2023 10:10	04/04/2023 11:10		1	0	0	0
4	04/04/2023 10:10	04/04/2023 11:10		2	0	0	0
5	04/04/2023 10:10	04/04/2023 11:10		3	7	5	7
6	04/04/2023 09:10	04/04/2023 10:10		4	1	0	1
7	04/04/2023 10:10	04/04/2023 15:10		5	3	5	5
8	04/04/2023 10:10	04/04/2023 11:10		6	0	0	0
9	04/04/2023 10:10	06/04/2023 11:10		7	0	0	0
10	06/04/2023 10:10	06/04/2023 11:10
11	06/04/2023 10:10	06/04/2023 11:10
12	06/04/2023 09:10	06/04/2023 10:10
13	05/04/2023 10:10	06/04/2023 11:10
Sheet2

Cell Formulas
Range	Formula
E3:E9	E3	=SUMPRODUCT((($A$3:$A$13)-INT($A$3:$A$13)>=E$2)*(WEEKDAY($A$3:$A$13)=D3))
F3:F9	F3	=SUMPRODUCT((($B$3:$B$13)-INT($B$3:$B$13)<=F$2)*(WEEKDAY($B$3:$B$13)=D3))
G3:G9	G3	=MAX(E3:F3)

Thanks for your help,
it's absolutely going in the right direction, even though I think we still don't totally understand each other.
The table on the right doesn't need to show entry and exit.
Only presence at a given time, that I get from the columns you put in A:B.

Even though we are not talking hospitality imagine it that way:
guest checks in at timestamp A2 and checks out at C2. The time spent in between can be a few hours or several days.
I need with only this information a whole table that visualizes the occupancy rate per day and time of the day from the timestamps in A2:B2.

The idea is to use this analysis to understand when and where to make some services available, depending on what times and days there's more need as summed up in tabel E:G.
Ideally the level of detail should be 15 minutes, but if I get way to work around the midnight / dayshift issue I should be able to make it more detailed myself.

 

[AlanY]

Cols E & F just to show you the make up of the formula that can be combined into one like this, the question is dose it give you the desired answer,

Book1
	A	B	C	D	E	F
1					Between
2	Enter	Exit		Weekday	09:00	12:30
3	04/04/2023 10:10	04/04/2023 11:10		1	0
4	04/04/2023 10:10	04/04/2023 11:10		2	0
5	04/04/2023 10:10	04/04/2023 11:10		3	7
6	04/04/2023 09:10	04/04/2023 10:10		4	1
7	04/04/2023 10:10	04/04/2023 15:10		5	5
8	04/04/2023 10:10	04/04/2023 11:10		6	0
9	04/04/2023 10:10	06/04/2023 11:10		7	0
10	06/04/2023 10:10	06/04/2023 11:10
11	06/04/2023 10:10	06/04/2023 11:10
12	06/04/2023 09:10	06/04/2023 10:10
13	05/04/2023 10:10	06/04/2023 11:10
Sheet2

Cell Formulas
Range	Formula
E3:E9	E3	=MAX(SUMPRODUCT((($A$3:$A$13)-INT($A$3:$A$13)>=E$2)*(WEEKDAY($A$3:$A$13)=D3)),SUMPRODUCT((($B$3:$B$13)-INT($B$3:$B$13)<=F$2)*(WEEKDAY($B$3:$B$13)=D3)))