Countifs now working with date format

[agamino]

I have the current date format and cannot get a count between two dates. Here is my formula:

=COUNTIFS(N2:N11253,">=01-Apr-15 00:00:00",N2:N11253,"<=30-Apr-15 00:00:00")

any help is appreciated.

 

[Jonmo1]

=ISNUMBER(N2:N11253)=FALSE

That's not exactly what I was asking for...

Need to do
=ISNUMBER(N2) and fill down, so you get ISNUMBER(N3) ISNUMBER(N4) etc...

Anyway, if that one returned false it at least means the one in N2 is NOT a real date.
It's reasonable to assume then that they're all not real dates.

Try copying a blank cell
Highlighting N2:N11253
Right Click - Paste Special - Values - Add - OK

 

[agamino]

Changing a cell in the range to general still produces "0."

 

[agamino]

OK, if no a real date, how can I can it?

 

[Jonmo1]

OK, if no a real date, how can I can it?

Try

Try copying a blank cell
Highlighting N2:N11253
Right Click - Paste Special - Values - Add - OK

 

[agamino]

OK, maybe I am not doing this right. When I copy a blank cell and highlight one of the cells that should be counted, I end up with a blank cell. I then manually type in the date format and it read the cell. Problem is with 11252 cells this could be a chore to redo all cells.

 

[Jonmo1]

You're missing a step in the process..

Copy a Blank Cell
Highlight the ENTIRE range of your dates N2:N11253
Right click
Click on Paste Special (don't just use the little icons that slide out)
Select Values
Select ADD <- I think this is the step you missed.
Cllick OK

 

[agamino]

Is it "Values" or "Formatting & Numbers?" Tried "Values" and since the cell has a "blank" value all cells go blank.

 

[Jonmo1]

You have to make 2 selections in there...

Values
AND
Add

 

[agamino]

Copying a blank cell and pasting through "Paste Special", Selecting VALUES and ADD produce 41837.7083333333, is this correct?

 

[Jonmo1]

Yes..

Now, you can go to format cells and apply your desired date/time format.