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?
Ramon Haagen , above formula was only mock-up, you shall use your actual ranges and criteria cells. In your case that's
=COUNTIFS(tbl_Activity_registration_1[Saved_on],">="&$A3,tbl_Activity_registration_1[Saved_on],"<"&$A3+1)
But I don't see any time with your dates, thus that could be slimly
=COUNTIFS(tbl_Activity_registration_1[Saved_on],$A2)
(column Y in attached)
5 Replies
Hi Ramon Haagen ,
In Excel dates are integer numbers and the time is decimal part. Thus, to count dates only you may use something like
INT(<date>)
in your formula. If apply to the range, you may use SUMPRODUCT. If with COUNIFS, that could be like
=COUNTIFS(<range>,">="&<date>,<range>,"<"&<date>+1)
since you caný apply INT to the range.
SergeiBaklan Thanks for the advice. However I have been trying to make it work for the last hour without much success and I do not understand why.
I have attached the file(password 1234).
Basicly in sheet productivity report in cell E2 I would like that the formula counts from sheet acces database column D the amount of number 1's on the specific date are in there.
Could you help me with this?
Ramon Haagen , above formula was only mock-up, you shall use your actual ranges and criteria cells. In your case that's
=COUNTIFS(tbl_Activity_registration_1[Saved_on],">="&$A3,tbl_Activity_registration_1[Saved_on],"<"&$A3+1)
But I don't see any time with your dates, thus that could be slimly
=COUNTIFS(tbl_Activity_registration_1[Saved_on],$A2)
(column Y in attached)
