Forum Discussion
Countifs and how to ignore timestamp
I want to use countifs to count a specific criteria on a certain date however in the date cell is also a timestamp. Because of this it doesn't count any other cell with this date because the time sta...
To use COUNTIFS you cannot change the datestamp as it will become an array rather than a range reference. The way around is to test the timestamp against both the start and the end of the day.
= COUNTIFS( Date,">="&INT(Target), Date,"<"&INT(Target)+1 )
An alternative is to use an array formula to test the date only
= COUNT( IF( INT(Date)=INT(Target), 1 ) )
If you need to test every date in a list against the list, this can be done using
= COUNTIFS( Date,">="&INT(Date), Date,"<"&INT(Date)+1 )
with CSE or implicit intersection.