Countif for cells with this date format 2013-05-31 04:00:00.000

I have a column of dates with times in the following format.  I want to count the rows that that are greater than or equal to a certain date and time.  However, the result shows 0.

date and time format I have:
2013-05-31 04:00:00.000
dorianitAsked:
Who is Participating?
 
barry houdiniCommented:
If you get zero it may be that your date/time values are text formatted - try converting by doing this:

Select column of data then use

Data > text to columns > finish

Now try fanpages' suggestion again

alternatively try this version without converting

=SUMPRODUCT((A1:A5+0>C1)+0)

where C1 contains your cutoff date/time

regards, barry
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Your COUNTIF() formula should look something like this:

=COUNTIF(A1:A5,">=2013-05-31 04:00:00.000")

or for dates greater than, or equal to, 1 June 2013 at 7:24am exactly:

=COUNTIF(A1:A5,">=2013-06-01 07:24:00.000")

BFN,

fp.
Q-28149149.xls
0
 
dorianitAuthor Commented:
I don't see what I'm doing wrong.  I tried it and returns 0.
0
 
[ fanpages ]IT Services ConsultantCommented:
Can you attach your workbook for review?
0
 
Rgonzo1971Commented:
Hi,

You could use

=COUNTIF(A1:A5,">="&DATEVALUE("2013-06-01 07:24"))

if that doesn't work ( it depends on your localization for me it would be "1.6.2013 07:24")

put the Date in another cell for example C1 and then

=COUNTIF(A1:A5,">="&C1)

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.