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 stamp is different.
Any idea how to get rid of the time stamp or have the countifs not count the timestamp?
3 Replies
Ramon Haagen , I guess that's duplication of https://techcommunity.microsoft.com/t5/Excel/Countifs-and-how-to-ignore-timestamp/m-p/401274#M28508
Seems so!
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.
