Countif a datetime group is greater than a particular time, regardless of date

[admvlad]

I am trying to track the start time of certain events. So I need to know if a time is after 4pm, however the format is a a date time group. How can I get a count of any start time after 4pm regardless of what the date is? The cell is formatted like this :
4/28/2024 10:52:00 AM

 

[awoohaw]

assume your date is in cell B2:

Formula in C2:

=Mod(B2,1)>16/24

will give yoiu true or false for any date with time greater than 4pm (not including 4 pm)

Book1
	A	B	C	D
1
2		2024-04-28 10:52:00 AM	FALSE	8
3		2024-04-29 11:52:00 AM	FALSE
4		2024-04-30 12:52:00 PM	FALSE
5		2024-05-01 01:52:00 PM	FALSE
6		2024-05-02 02:52:00 PM	FALSE
7		2024-05-03 03:52:00 PM	FALSE
8		2024-05-04 04:52:00 PM	TRUE
9		2024-05-05 05:52:00 PM	TRUE
10		2024-05-06 06:52:00 PM	TRUE
11		2024-05-07 07:52:00 PM	TRUE
12		2024-05-08 08:52:00 PM	TRUE
13		2024-05-09 09:52:00 PM	TRUE
14		2024-05-10 10:52:00 PM	TRUE
15		2024-05-11 11:52:00 PM	TRUE
16		2024-05-13 12:52:00 AM	FALSE
17		2024-05-14 01:52:00 AM	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=MOD(B2,1)>16/24
D2	D2	=SUM(--(MOD($B$2:$B$17,1)>16/24))
B3:B17	B3	=B2+1+1/24

 

[admvlad]

assume your date is in cell B2:

Formula in C2:

=Mod(B2,1)>16/24

will give yoiu true or false for any date with time greater than 4pm (not including 4 pm)

Book1
	A	B	C	D
1
2		2024-04-28 10:52:00 AM	FALSE	8
3		2024-04-29 11:52:00 AM	FALSE
4		2024-04-30 12:52:00 PM	FALSE
5		2024-05-01 01:52:00 PM	FALSE
6		2024-05-02 02:52:00 PM	FALSE
7		2024-05-03 03:52:00 PM	FALSE
8		2024-05-04 04:52:00 PM	TRUE
9		2024-05-05 05:52:00 PM	TRUE
10		2024-05-06 06:52:00 PM	TRUE
11		2024-05-07 07:52:00 PM	TRUE
12		2024-05-08 08:52:00 PM	TRUE
13		2024-05-09 09:52:00 PM	TRUE
14		2024-05-10 10:52:00 PM	TRUE
15		2024-05-11 11:52:00 PM	TRUE
16		2024-05-13 12:52:00 AM	FALSE
17		2024-05-14 01:52:00 AM	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=MOD(B2,1)>16/24
D2	D2	=SUM(--(MOD($B$2:$B$17,1)>16/24))
B3:B17	B3	=B2+1+1/24

Thank you awoohaw. That works perfectly!

 

[awoohaw]

Thank you awoohaw. That works perfectly!

Happy to help Thanks for the feedback.