Solved

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

Posted on 2013-06-05
5
347 Views
Last Modified: 2013-06-08
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
Comment
Question by:dorianit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 187 total points
ID: 39224469
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
 

Author Comment

by:dorianit
ID: 39224482
I don't see what I'm doing wrong.  I tried it and returns 0.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 187 total points
ID: 39224516
Can you attach your workbook for review?
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 146 total points
ID: 39224654
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 167 total points
ID: 39225172
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question