Countifs -> Dates in 2 cells are equal & before time

[nTn]

Hi everyone,

I'm trying to count the amount of times 2 different cells have the same date and also the times in a 3rd cell need to match and the 4th cell need to be before/after a certain time.
In my formula I've only managed to put in the times, so if the date is incorrect it will be counted anyway. Do you know a fix or maybe a better way of doing it? Any input very appreciated.

For example:

Cell D2: =COUNTIFS(B9:B503;"="&TIME(7;0;0);D9:D503;"<="&TIME(7;0;0))
Cell D3: =COUNTIFS(B9:B503;"="&TIME(7;0;0);D9:D503;">="&TIME(7;0;1);D9:D503;"<="&TIME(8;0;0))
Cell D4: =D2+D3
Cell D5: =COUNTIFS(B9:B503;"="&TIME(7;0;0);D9:D503;">="&TIME(8;0;1))

 

[Skybluekid]

Try this:

Book1
	B	C	D	E
1	Due	Arrived
2	02/01/2023 07:00	02/01/2023 07:00
3	03/01/2023 07:00	04/01/2023 07:00
4	04/01/2023 07:00	04/01/2023 07:15
5	05/01/2023 10:00	05/01/2023 11:00
6	06/01/2023 07:00	06/01/2023 08:15
7
8	Due 7:00 and arrived before 7:00			1
9	Due 7:00 and arrived between 07:00:01 and before 08:00			1
10	Due 7:00 and arrived before 08:00			2
11	Due 7:00 and after 08:00.01			2
12	Arrived the wrong day			1
Sheet1

Cell Formulas
Range	Formula
E8	E8	=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)<=0.29167)))
E9	E9	=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)>=0.291678)*(MOD($C$2:$C$6,1)<=0.333333)))
E10	E10	=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)<=0.333333)))
E11	E11	=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)>=0.33)))
E12	E12	=SUMPRODUCT(--(INT($B$2:$B$6)<>INT($C$2:$C$6)))

 

[ExceLoki]

here's another option. i also created a suggested set, but this is if you are looking for how far over due time or wanting to compare specific times.
------------------

Book1
	A	B	C	D	E	F	G	H
1				previous	expected	new	suggested	suggested description
2	due 7 arrive before 7			2	1	0	0	arrive early same day
3	due 7 arrive 7-8			1	1	2	1	arrive on time same day
4	due 7 arrive before 8			3	2	2	2	arrive within hour on time same day
5	due 7 arrive after 8			1	1	1	1	arrive over hour late same day
6	arrive wrong day				1	1	1	arrive wrong day
7
8	date due	time due	date arrived	time arrived	date diff	time diff
9	2/1/2023	7:00	2/1/2023	7:00:00	0	0.00
10	3/1/2023	7:00	4/1/2023	7:00:00	-31	0.00
11	4/1/2023	7:00	4/1/2023	7:15:00	0	-15.00
12	5/1/2023	10:00	5/1/2023	11:00:00	0	-60.00
13	6/1/2023	7:00	6/1/2023	8:15:00	0	-75.00
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIFS(E9:E503,"=0",B9:B503,"="&TIME(7,0,0),D9:D503,"<"&TIME(7,0,0))
G2	G2	=COUNTIFS(E9:E503,"=0",F9:F503,">0")
F3	F3	=COUNTIFS(E9:E503,"=0",B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(7,0,0),D9:D503,"<"&TIME(8,0,0))
G3	G3	=COUNTIFS(E9:E503,"=0",F9:F503,"=0")
F4,D4	F4	=F2+F3
G4	G4	=COUNTIFS(E9:E503,"=0",F9:F503,"<0",F9:F503,">=-60")
F5	F5	=COUNTIFS(E9:E503,"=0",B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(8,0,1))
G5	G5	=COUNTIFS(E9:E503,"=0",F9:F503,"<-60")
F6	F6	=COUNTIFS(A9:A503,"<>",E9:E503,"<>0")
G6	G6	=COUNTIFS(A9:A503,"<>",E9:E503,"<>0")
D2	D2	=COUNTIFS(B9:B503,"="&TIME(7,0,0),D9:D503,"<="&TIME(7,0,0))
D3	D3	=COUNTIFS(B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(7,0,1),D9:D503,"<="&TIME(8,0,0))
D5	D5	=COUNTIFS(B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(8,0,1))
E9:E13	E9	=A9-C9
F9:F10,F12:F13	F9	=(B9-D9)*1440
F11	F11	=IF(((B11-D11)*1440)<0,(B11-D11)*1440,(B11-D11)*1440)

 

[nTn]

Thanks to the both of you for taking your time to help me.
@ExceLoki your solution seemed to fit better for my scenario and I've now applied it with a few adjustments. Works great and I can track the data correctly!

 

[ExceLoki]

glad to help. thanks for the feedback