Forum Discussion
Solved
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 c...
=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.
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
When I plug in the formula and just change the range to H:H, it gives me a #value error
I'd do not apply the formula to entire column, SUMPRODUCT shows quite poor performance in such case. If not dynamic range take with some gap
=SUMPRODUCT(
(MOD(H2:H1900,1) >= TIME(14,0,0) ) *
(MOD(H2:H1900,1) <= TIME(22,0,0) )
)- Hey Sergei, Hope you could help me, for Inventory reasons Im trying to use countif to count dates that have a time, the work around that I found was to separate the date from time use =COUNTIFS('In-Hub Inventory'!V:V,B13,'In-Hub Inventory'!A:A,"="&C21) in C21 I have today date. Can I count the dates with the time example: 2024-01-31 17:21:45, what formula can I use to be able to count that type of date without separating them?
thank you in advance!!
