COUNTIF cell contains date

Hi GC,

If the only states of cell in that column are: empty, contains date, contains "x", then this should work:

=COUNTA(H3:H459)-COUNTIF(H3:H459,"x")

Regards,

Barry

If you don't want to change your "text" dates then here are two possibilities I've tested here that work on dates entered as text:

=COUNTIF(B,REGEX("^\d."))

Will count anything that starts with a digit followed by more characters.

=COUNTIF(B,REGEX("^\d\d\.\d\d\.\d\d"))

Will count anything started by two digits followed by a . followed by two digits followed by a . followed by two digits.

Note that you need to "escape" the . with a \.

The B needs to be changed to whatever range you are using.

SG

The formulas below use column B, yours might be F.

I thought this would be as simple as

=COUNTIF(B,">="&DATE(1,1,1))

which should count all dates >= 01/01/0001 but this formula gives me incorrect results, not counting all dates. It appears to work when written as

=COUNTIF(B,">=1/1/1")

but neither will work for you unless your dates are actually dates. Your cell format is not a standard date&time format on my system. It could be a custom format you created but I am guessing it is text. If it is text, you can count cells that have the form ##.##.## . This should still work even if they are custom formatted dates but I did not test it.

=COUNTIF(B,REGEX("\d\d.\d\d.\d\d"))

Pretty straightforward message. It's telling you that the formula in the selected row is set to get information from the cell containing the formula,

Change the range of cells in the formula to end with the cell above the one containing the formula,or, if you have used a full column (letter only) reference to identify the range, convert the bottom row to a Footer row. For many functions, this keeps the full column range from extending into the footer row and causing that self-reference error.

Regards,

Barry

From the looks of things it should have worked after changing the formula to reference the correct column in your spreadsheet (not column B from what I can tell). What was the error message?