COUNTIF Function and Blank Date Cells

[Atkey]

I am attempting to generate a table that would show the number of dates in a column that are equal to or less that today' date. Some cells are blank and the formula is counting these blank cells. How would I get my formula to just count dates in the required range and ignore the blanks?

I am using =COUNTIF(M2,"<="TODAY())

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
Try

=countifs(m:m,"<="&today(),m:m,"<>")

 

[Atkey]

Fluff,

Thanks, unfortunately that didn't work. The formula is still counting blank cells. The cells are formatted as dates would a blank be seen as a date less then today?

 

[Joe4]

Are the values in those cells hard-coded, or the result of formulas?
If hard-coded, can you identify one of these supposedly "blank cells", and see what the length is?

I.e. if it was cell M2, enter this formula in any blank cell and tell us what it returns:

=LEN(M2)

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Atkey]

The data in the cells come from a different sheet. the LEN function delivers a result of 1.

 

[Joe4]

The data in the cells come from a different sheet. the LEN function delivers a result of 1.

That means it is not truly blank. There is "something" in there, whether it be a space, special character, or something else.

The CODE should identify exactly what is in there, i.e.

=CODE(M2)

What does that return?

 

[Atkey]

It cells are showing the source formula =Master[Leave Date]

 

[Joe4]

It cells are showing the source formula =Master[Leave Date]

That doesn't answer my last question.

 

[Atkey]

That doesn't answer my last question.

Apologies. The CODE function is returning 48.