Count if + Contains?

Drew Cooper

I have a column of dates. My data sometimes will give a "fake date" of 01/01/01. I'm trying to have that specific date counted. My formula is:

=COUNTIFS({FLEETCARE UPLOAD Range 2}, CONTAINS("01/01/01", @cell))

But it doesn't seem to be working?

Any ideas?

Paul Newcome

Ok. If you are outputting a date then you need to search for a date.

=COUNTIFS({FLEETCARE UPLOAD Range 2}, @cell = DATE(2001, 01, 01))

Paul Newcome

Are you getting an error or an inaccurate count? What is the formula that outputs the "fake date"?

Drew Cooper

The count always returns 0. Even if I have some dates listed with that weird date of 01/01/01

Paul Newcome

What is the formula that outputs the "fake date"?

Drew Cooper

The date is imported as: 1/1/0001 12:00:00 AM(MST)

Then I'm using the following formula to just grab the date and exclude the time stamp:

DATE(VALUE(MID(Last@row, FIND("/", Last@row, 4) + 1, 4)), VALUE(LEFT(Last@row, FIND("/", Last@row) - 1)), VALUE(MID(Last@row, FIND("/", Last@row) + 1, FIND("/", Last@row, 4) - (FIND("/", Last@row) + 1))))

This leave me with 01/01/01 , and I just want to count how many have that date. I tried: =COUNTIFS({FLEETCARE UPLOAD Range 2}, CONTAINS("01/01/01", @cell)) but it always produces 0

Paul Newcome

Ok. If you are outputting a date then you need to search for a date.

=COUNTIFS({FLEETCARE UPLOAD Range 2}, @cell = DATE(2001, 01, 01))

Drew Cooper

THANK YOU!!!!

Paul Newcome

Happy to help. 👍️