Forum Discussion
Excel Countif with date and time
| 3/5/2024 21:40 |
| 3/5/2024 21:40 |
| 3/5/2024 21:40 |
| 3/5/2024 21:40 |
| 3/5/2024 21:40 |
| 3/5/2024 21:41 |
| 3/5/2024 21:41 |
| 3/5/2024 22:41 |
This is my sample data in range A12:A19
I have a countif function that looks like so:
=COUNTIF(A12:A19,"<"&TIME(22,0,0))
Howewver this returns 0.
I am ultimately trying to count the number of cells within a time range (2PM -10PM) but I can't get the countif to work even for a basic example.
For reference this is the format of the contents in the cell:
3/5/2024 9:41:53 PM
=SUMPRODUCT((MOD(A2:A19,1)<22/24)*(MOD(A2:A19,1)>14/24))
In this formula 22/24 is for 10 PM and 14/24 is for 2 PM. The range A2:A19 can be changed according to the actual size of the database. I can only guess that there is a text value in at least one cell in the range in column H. If your range is 1300 cell i'd reference e.g. H1:H1300 instead of H:H because the latter refers to 1048576 cells. It's easier to check if there is a text value in range H1:H1300.
10 Replies
Back t
=SUMPRODUCT( (MOD(A12:A19,1) >= TIME(14,0,0) ) * (MOD(A12:A19,1) <= TIME(22,0,0) ) )o main question
=SUMPRODUCT(N(MOD(A12:A19,1)<22/24))
An alternative could be SUMPRODUCT. 22/24 refers to the 22nd of 24 hours of a day. MOD(A12:A19,1) returns 0,9028 for 03.05.2024 21:40 because 21:40 equals 0,9028 of 24 hours. The numbervalue of 03.05.2024 21:40 would be 45415,9028 and 45415 is removed with the MOD function.
- How would that formula change if I was searching through an entire column for a time range. For instance, I have around 1300 cells with dates from 12 AM to 11:59 PM. I am trying to count the number of cells from 6AM-2PM and 2PM-10PM
When I plug in the formula and just change the range to H:H, it gives me a #value error=SUMPRODUCT((MOD(A2:A19,1)<22/24)*(MOD(A2:A19,1)>14/24))
In this formula 22/24 is for 10 PM and 14/24 is for 2 PM. The range A2:A19 can be changed according to the actual size of the database. I can only guess that there is a text value in at least one cell in the range in column H. If your range is 1300 cell i'd reference e.g. H1:H1300 instead of H:H because the latter refers to 1048576 cells. It's easier to check if there is a text value in range H1:H1300.
