Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default count cells with time and date

Hi, could somebody please help!

cell contain date and time
e.g. " 22/08/2009 14:34"

loads of them in the same column lets say column "A"

I have 5000+ cells per day transfered from another system,

I need to know everyday, how many of these 5000 cell iin column A is from
22/08/2009, 23/08/2009, 24/08/2009....etc (can ignore the time)

I have tried using formula
=SUM(COUNTIF(A:A,{"22/08/2009"}))

but it does not work, as the time within the cell is causing non exact
match, is there a way to go round this, and count how many cells is from each
date?
I have formated the cell to show dates only but the cell still contain time,
and still does not work!

all your help is greatly appreciated, thx a million

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default count cells with time and date

Hi

=COUNTIF(A:A,"22/8/2009")

or use a cell ref where b1 contains the date

=COUNTIF(A:A,B1)

Mike

"count celld with date and time" wrote:

Hi, could somebody please help!

cell contain date and time
e.g. " 22/08/2009 14:34"

loads of them in the same column lets say column "A"

I have 5000+ cells per day transfered from another system,

I need to know everyday, how many of these 5000 cell iin column A is from
22/08/2009, 23/08/2009, 24/08/2009....etc (can ignore the time)

I have tried using formula
=SUM(COUNTIF(A:A,{"22/08/2009"}))

but it does not work, as the time within the cell is causing non exact
match, is there a way to go round this, and count how many cells is from each
date?
I have formated the cell to show dates only but the cell still contain time,
and still does not work!

all your help is greatly appreciated, thx a million

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default count cells with time and date

Say A1 thru A30 contains:

19/08/2009 00:16
19/08/2009 00:00
15/08/2009 14:33
14/08/2009 01:39
14/08/2009 19:49
18/08/2009 06:06
22/08/2009 12:33
22/08/2009 08:06
20/08/2009 23:07
17/08/2009 09:05
15/08/2009 14:56
23/08/2009 08:56
23/08/2009 08:27
14/08/2009 10:38
19/08/2009 22:57
14/08/2009 13:11
21/08/2009 14:51
18/08/2009 05:52
15/08/2009 07:29
20/08/2009 22:58
17/08/2009 02:26
18/08/2009 18:14
14/08/2009 10:25
21/08/2009 00:54
18/08/2009 04:23
15/08/2009 09:54
20/08/2009 19:18
18/08/2009 22:07
16/08/2009 08:09
15/08/2009 18:48

and we want to count the number of 22/8/2009's

If we displayed the values as numbers, we would see:

40044.0115
40044.0006
40040.6067
40039.0693
40039.8260
40043.2548
40047.5234
40047.3380
40045.9635
40042.3791
40040.6228
40048.3724
40048.3524
40039.4432
40044.9563
40039.5495
40046.6194
40043.2447
40040.3118
40045.9573
40042.1019
40043.7598
40039.4345
40046.0376
40043.1828
40040.4126
40045.8046
40043.9217
40041.3396
40040.7839

So we really need to count the number of 40047's

Try:

=SUMPRODUCT(--(INT(A1:A30)=40047))

--
Gary''s Student - gsnu200901


"count celld with date and time" wrote:

Hi, could somebody please help!

cell contain date and time
e.g. " 22/08/2009 14:34"

loads of them in the same column lets say column "A"

I have 5000+ cells per day transfered from another system,

I need to know everyday, how many of these 5000 cell iin column A is from
22/08/2009, 23/08/2009, 24/08/2009....etc (can ignore the time)

I have tried using formula
=SUM(COUNTIF(A:A,{"22/08/2009"}))

but it does not work, as the time within the cell is causing non exact
match, is there a way to go round this, and count how many cells is from each
date?
I have formated the cell to show dates only but the cell still contain time,
and still does not work!

all your help is greatly appreciated, thx a million

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default count cells with time and date

One mo

=sumproduct(--(text(a1:a100,"mmddyyyy")="08222009"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===============
I think I'd consider using a pivottable, too.

You could group by date (and ignore the time).

count celld with date and time wrote:

Hi, could somebody please help!

cell contain date and time
e.g. " 22/08/2009 14:34"

loads of them in the same column lets say column "A"

I have 5000+ cells per day transfered from another system,

I need to know everyday, how many of these 5000 cell iin column A is from
22/08/2009, 23/08/2009, 24/08/2009....etc (can ignore the time)

I have tried using formula
=SUM(COUNTIF(A:A,{"22/08/2009"}))

but it does not work, as the time within the cell is causing non exact
match, is there a way to go round this, and count how many cells is from each
date?
I have formated the cell to show dates only but the cell still contain time,
and still does not work!

all your help is greatly appreciated, thx a million


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y nigeo Excel Discussion (Misc queries) 3 April 1st 09 10:38 PM
count the occurrences of a month in a date&time cell Tom Excel Worksheet Functions 6 February 14th 08 06:05 PM
Count minutes between two time/date fields safetymast Excel Discussion (Misc queries) 8 December 20th 07 05:53 PM
Count number of cells with date <today's date Cachod1 New Users to Excel 2 January 28th 06 03:37 AM
count the number of cells with a date <= today's date Cachod1 New Users to Excel 3 January 27th 06 10:14 PM


All times are GMT +1. The time now is 08:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"