Solved

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

Posted on 2013-06-05
5
355 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 51

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

Technology Partners: 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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

718 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