Forum Discussion
Excel Countif with date and time
=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.
=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.
OliverScheurich I wonder if you could help me with my own COUNTIFS issue.
I'm attempting to make COUNTIFS work in a way that will count how many cells from D2:P2 have dates in the past, but only if the date in C2 is in the future.
The formula I have so far is:
=COUNTIFS(C2,">="&TODAY(), D2:P2,"<="&TODAY())
This is returning #VALUE! and I can't seem to work out where I'm going wrong with the formula.
Thanks in advance.
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!!
