Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y | Excel Discussion (Misc queries) | |||
count the occurrences of a month in a date&time cell | Excel Worksheet Functions | |||
Count minutes between two time/date fields | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |