Solved

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

Posted on 2013-06-05
5
328 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
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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now