Counting the number cells between two dates

Hi guys,

Hope someone can help with this, I'm pretty sure it'll be quite a simple one.

Column A:A contains a list dates, I want to use a formula to count the 
number of cells which contain a date between 01/01/05 - 31/01/05.

Any ideas,

Many thanks,
Dave
0
dave871 (680)
3/16/2005 12:27:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
374 Views

Similar Articles

[PageSpeed] 11

Try:

=SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=--
"1/31/05"))

BTW - I'm using American date formats in mine.

HTH
Jason
Atlanta, GA


>-----Original Message-----
>Hi guys,
>
>Hope someone can help with this, I'm pretty sure it'll 
be quite a simple one.
>
>Column A:A contains a list dates, I want to use a 
formula to count the 
>number of cells which contain a date between 01/01/05 - 
31/01/05.
>
>Any ideas,
>
>Many thanks,
>Dave
>.
>
0
jasonjmorin (551)
3/16/2005 2:26:25 PM
One way:

=COUNTIF(A:A,">="&DATE(2005,1,1))-COUNTIF(A:A,">"&DATE(2005,1,31))
or if you can use less than the whole column:

=SUMPRODUCT(--(TEXT(A1:A999,"yyyymm")="200501"))




Dave wrote:
> 
> Hi guys,
> 
> Hope someone can help with this, I'm pretty sure it'll be quite a simple one.
> 
> Column A:A contains a list dates, I want to use a formula to count the
> number of cells which contain a date between 01/01/05 - 31/01/05.
> 
> Any ideas,
> 
> Many thanks,
> Dave

-- 

Dave Peterson
0
ec357201 (5290)
3/16/2005 2:27:30 PM
Try...

=COUNTIF(A:A,">=1/1/2005")-COUNTIF(A:A,">1/31/2005")

Hope this helps!

In article <A4B76282-D9BD-4335-94E6-0D551B6AC710@microsoft.com>,
 "Dave" <Dave@discussions.microsoft.com> wrote:

> Hi guys,
> 
> Hope someone can help with this, I'm pretty sure it'll be quite a simple one.
> 
> Column A:A contains a list dates, I want to use a formula to count the 
> number of cells which contain a date between 01/01/05 - 31/01/05.
> 
> Any ideas,
> 
> Many thanks,
> Dave
0
domenic22 (716)
3/16/2005 2:30:21 PM
Reply:

Similar Artilces:

Check date does not G/L posting date
Is there a control in the A/P module to prevent the check date from being different than the General Ledger date? Use batch level posting dates. -- Charles Allen, MVP "Jack Cieniewicz" wrote: > Is there a control in the A/P module to prevent the check date from being > different than the General Ledger date? No because there are times you need the check date to be different -- ie manual checks being entered after the fact. "Jack Cieniewicz" <Jack Cieniewicz@discussions.microsoft.com> wrote in message news:8D0ECC3F-840E-46D2-AB7B-13DA652CDA0A@microso...

Linking Formats Across Cells
anyone know if I can link formats across cells? For example, if I fill a cell with a color, can I link it to another cell that will do the same ? thanks. Try the FormatPainter, the little PaintBrush icon up in the tool bar...........click on your colored cell, then click on the icon, then click and drag on the cells you want to be the same........... Vaya con Dios, Chuck, CABGx3 "RB" <RB@discussions.microsoft.com> wrote in message news:FE8ABE47-2671-447C-8CC6-2D784EEECE00@microsoft.com... > anyone know if I can link formats across cells? For example, if I fill a cell ...

How do I connect cell color to bar chart color?
I have seen an excel spread sheet bar chart where the colors of the bars were connected to the color of the cell. I was not able to determine how that was accomplished and the author of the spread sheet no longer works for the company that sent me the data. Does anyone know how this can be done? Hi Bob, It's possible this was done using a vba macro. For a non VBA approach have a look at Jon's examples of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy Bob Freerks wrote: > I have seen an excel spread sheet bar chart where the color...

Numbers dont display or add up correctly in excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I type in 1000 and it divides all numbers by 100, 23 becomes 0,23. Then if I Sum a list of numbers it doesn't ad up coretly (even the wrong numbers don't add up) Excel doesn't work for me - although the product was provided (bundled) by Apple - apple won't support it - and neither will Microsoft so I end up with no-person to talk to - amazing. Any help from anyone would be appreciated. Thanks On 6/5/09 6:35 AM, in article 59b75f96.-1@webcrossing.caR9absDaxw, "Clive@officeformac.com" <Cli...

SUMIF
Greetings, I want to SUMIF if the value of cells are between 5000 and 10000? Note: I am not summing anything. I have already SUM a colum. But for the next phase of my project, I have used SUMIF. If it is under 5000, it is displayed in a cell. I then take that sum and use it elsewhere. If it is between 5000 and 10000, I need to have it displayed in that cell and then it will be used in a different functuon elsewhere. I know that if I want to add amounts under 5000 it would be: =SUMIF(I10,"<5000.00",I10) but what is the symbol or word and way to write "betwee...

Number countdown
Hello, How would I write a macro for something like this: Number countdown from 144: Each page would have a number. ex. Page 1 "144", Page 2 "143" ....etc I just want a BIG number on a whole page letter size. But typing 144 or more numbers seems exhausting. It would be much easier with a macro. Thanks for your help See http://www.gmayor.com/Numbered_labels.htm Yves "Damian" <Damian@discussions.microsoft.com> wrote in message news:700173B2-5992-40C9-87D7-FCDA40B2AC4B@microsoft.com... > Hello, > How would I write a macro for so...

Extract phone number front block of text
I have a webpage that lists business names, address, phone number, etc. There is no definite pattern to how they entered the text. What I' like to do is something like.... -Search cell A1 for "-", return the 3 characters to the left of th "-".- In the next column do... -Search cell A1 for "-", return the 4 characters to the right of th "-".- Then I can concatenate the two and add in the area code. TIA guys/gals. PS. I tried searching but didn't know exactly what to search for an didn't get very far -- Tec ------------------------...

Copy one field in two or more fields
Hi i have a tableA with one field and another tableB with two fields. TableA Name a b c d e f .. ... I want copy all values alternatively, in an another tableB with two fields. The results should be: TableB NameA NameB a b c d e e .. Anyone can be help me ? Thank you in advance Best regards Diego -- Message posted via http://www.accessmonster.com Diego via AccessMonster.com wrote: >i have a tableA with one field and another tableB with two fields. > >TableA >Name > a > b > c ...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

Is there a way to protect just one cell, not the whole sheet?
The books I read seem to indicate so, but say you first must turn on the 'Protect Sheet' option. When I do that, everything is protected. Any help much appreciated. Hi try the following: - first select all cells you don't want to protect - goto 'Format - Cells - Protection' and uncheck 'Locked' - now protect your sheet -- Regards Frank Kabel Frankfurt, Germany "Fred Exley" <fexly221@msn.com> schrieb im Newsbeitrag news:10hsd5t42ghdt65@corp.supernews.com... > The books I read seem to indicate so, but say you first must turn on the > &...

SumProduct with date and time
I have finally found that with my formulas the calulations are based on the time. Can someone help me find the best way to correct this without using macros to strip the time out of the data. Bob 09/08/2007 01:05 13/08/2007 00:00 Bob Bill 10/08/2007 23:39 Bill Kyle 11/08/2007 07:36 Kyle Bob 16/07/2007 20:39 Bill 27/07/2007 19:59 Kyle 18/07/2007 16:51 Bob 14/07/2007 16:31 Bill 21/06/2007 16:46 Kyle 30/06/2007 16:55 Bob 12/06/2007 01:05 Bill 13/06/2007 23:39 1 2 0 1 1 Kyle 14/06/2007 07:36 1 1 1 1 1 Bob 12/01/2007 20:39 1 1 2 0 1 Bill 13/08/2006 19:59 Kyle 14/05/1999 16:51 These are t...

New discussion to increment the count, bwahahah
yeah ...

Counting with 2 Conditions
I created a report and would like to get a count of records with two conditions. Here is what I entered: =Abs(Sum(IIf([terminate]=-1,1,0)) And [caseload]="SA") This information is entered in an unbound text. I would like to get a total count where the terminate field (yes/not) is checked and the caseload meets a certain criteria. Thank you! TomP wrote: >I created a report and would like to get a count of records with two >conditions. Here is what I entered: > >=Abs(Sum(IIf([terminate]=-1,1,0)) And [caseload]="SA") > >This information is ent...

Two different values in a single cell...
Hi, Is it possible to have two different values (coming from another sheet) in a single cell? For example, 0.87 (0.97) In that case 0.87 comes from a cell on sheet1 and 0.97 from a cell on sheet2. Thanks for the help. Patrick, Hi You can concatenate two results into one cell. This makes it inot a text cell, however, so you cannot do maths on it quite so easily. To join the info, just use the CONCATENATE function, or the & sign =A2&" " &A3 You may also need the TEXT funtion, depending on what format you want the result to be -- Andy. "Patrick" <an...

Back ground Page numbers
In a multiple page excell file, how can I turn off the back ground page numbers? Thank you Gary Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Gary" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:100601c4994b$8c99e7b0$a401280a@phx.gbl... > In a multiple page excell file, how can I turn off the > back ground page numbers? Thank you Gary Gary, Not sure what you mean by "background page numbers." Do you mean a page number in either the header or footer? If so, go to File | Page Setup. Click on Header/Footer tab. Remo...

Change Label based on Two Combo Boxes
Hi, I currently have this code in AfterUpdate event for Combo2, hoping it would change label1, but it does not work. : If Me.Combo1.Value = "State" And Me.Combo2.Value = "2008" Or "2007" Or "2006" Then Me.Label1.Caption = "Select a State:" ElseIf Me.Combo1.Value = "City" And Me.Combo2.Value = "2009" Or "2010" Or "2011" Then Me.Label1.Caption = "Select a City:" Else Me.Label1.Caption = "Select a Number:" End If The label does not change when I make changes to the combo...

text in Bullets/points in cell
How can one write the text in bullets or points in the cell in excel 2007. Hold the ALT key, then on the NUMERIC KEYPAD, type 0149, then let go of the ALT key -- you'll have your bullet •<== works here, too! "kamal" wrote: > How can one write the text in bullets or points in the cell in excel 2007. ...

Java vs. C++ (Date class)
I'm porting some Java code into C++. Which class I should use instead Date class? Note that Date support miliseconds. Also, which class I should use instead of Vector class? std::vector? "Petar Popara" <my.fake@mail.net> wrote in message news:uG6VGpUfFHA.2424@TK2MSFTNGP09.phx.gbl... > > I'm porting some Java code into C++. Which class I should use instead Date > class? Note that Date support miliseconds. Oh dear. Both the Java and the C++ date classes do assorted odd things with time zones and "daylight saving"; neither is documented fully or ...

Page numbering #5
I have a report I have written in Excel using multiple sheets. some sheets have a single page, some have multiple pages. I want to put page numbers in the footer as I also have to put time date, filename in the footer due to company layout rules. All the page numbers are referred to in the index in a 1.1, 1.2, 1. type format and have to be done that way, again for company layout rul (Boy am I glad this is a contract!!) I can't work out how to have the page numbers working in consecutiv order where I have the multiple pages on a single sheet. Anyone have any ideas? Thanks Simo -- k...

Want to find max in a range then return a name from a cell
The spreadsheet has 5 people each person has a list describing thei sales performance. Each has their highest sale, highest over cost sale etc. I have a table with a describtion highest sale, highest over cost sal etc. I want the cell beside each title to calcute who has the highest figur and place their name in this cell. :confused: I have a spreadsheet with lists containing highest sale highest average sale etc. What I want to do is create a formula tha will find the max value within a list please note these values ar spread out and do not run on the spreadsheet side by side or one aft...

Excel 2003: Cell rounds 16 digit numbers- How to advoid rounding?
When I enter a 16 digit credit card number, excel rounds the last digit to 0; How do I format to reatin the exact number I entered (Have formatted cells to numbers, no decimal places now.) Hi, Format as text or precede your number with an apostrophe; it won't show up in the cell. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Max T." wrote: > When I enter a 16 digit credit card number, excel rounds the last digit to 0; > How do...

how to add a letter in front of each number in a cell
I am working on this project & I cant figure out how to add a letter in front of each number in a cell without clicking in to each individual cell and typing it in. (very time consuming and tedious) The problem is - it's not all the cells but a majority, all of the numbers are in the same column. To explain, these are documents in storage. Each item is assigned a number (not in any particular numerical order but they have to stay in the order they are. Example: X39655 X39656 X39711 39662 39664 39665 The last three numbers need the X in front of them. Any ideas? Thanks so muc...

error in excel help. how to select a cell after clicking the Select All button?
Hello "To see all the relationships on a worksheet, in an empty cell type = (equal sign), and then click the Select All button. Select the cell, and press Trace Precedents twice." is written in Excel 2002 help. It's not possible to "Select the cell" following the procedure. Or How to "select the cell" in the procedure? begin 666 0486.gif M1TE&.#EA%0`4`)$``/___V9XI0```)D`F2'Y! ``````+ `````5`!0```)* MC(^I`>T/GP&BUHBG<'MCIW%-]VF61Z'0A%DE,[:GQU+M@ \T,T-"KFO45)P> @8/AP96"R5TI)6MIBQ$@H]I'P3EEM-UO[KA9D10$`.P`` ` end Ma...

how to create a calendar to select a date in access 2007
I want to select a date from a pop up calendar on the form. Access 2007 automatically adds a date picker to a Date/Time field. -- Lynn Trapp MCP, MOS, MCAS "keithteri" wrote: > I want to select a date from a pop up calendar on the form. ...

Join Query
Hello, I am using Access 2003 with no ability to switch versions. I have two tables. 1 is the current inventory of Fuel Cards, and the second is the new inventory of Fuel Cards. I want to find all the NEW Fuel Cards that exist only in the second table. SO I have list A and List B. I want to find all the entries in B that are not in A. A has a two field Primary key. (Fuel Card # and FuelCard Provider) B does not have a key but for all intents and purposes, it has the same key ( though it is not defined within access). Here is the SQL code the wizard gave me. SELECT ex_FuelCardInpu...