Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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
0
dorianit
Asked:
dorianit
4 Solutions
 
[ 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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now