Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Count Date Range

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In a column I have a list of dates and I would like to count within that column range how many entries between Date 1 and Date 2. Could someone help me with this formula?

    Thanks
    Terry

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How 'bout an array formula...

    {=COUNT(IF((A2:A4>"1/31/02"+0)*(A2:A4<"3/1/02"+0),1))}


    Note1: This is an "exclusive between". Replace ">" and "<" with ">=" and "<=" for an "inclusive between".

    Note2: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.



    [ This Message was edited by: Mark W. on 2002-04-03 10:48 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 10:30, TClark14 wrote:
    In a column I have a list of dates and I would like to count within that column range how many entries between Date 1 and Date 2. Could someone help me with this formula?

    Thanks
    Terry
    Also:

    =COUNTIF(A1:A10,">="&E1)-COUNTIF(A1:A10,">"&E2)

    where A1:A10 houses dates, E1 Date 1, E2 Date 2, and E2 > E1.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    also
    =COUNTIF(A2:A4,">="&"1/31/02"+0)-COUNTIF(A2:A4,">"&"3/01/02"+0) for inclusive

    or
    =COUNTIF(A2:A4,">"&"1/31/02"+0)-COUNTIF(A2:A4,">="&"3/01/02"+0) for exclusive will do the trick.

    That twice!
    I will hit topic review before posting
    I will hit topic review before posting
    I will hit topic review before posting
    I will hit topic review before posting
    I will hit topic review before posting
    I will hit topic review before posting
    I will hit topic review before posting
    I will hit topic review before posting
    [ This Message was edited by: IML on 2002-04-03 11:11 ]

    [ This Message was edited by: IML on 2002-04-03 11:12 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have tried entering all the above formulas as you have them and my result is always 1. I know positively I should get a count of 4 for the month of April. Could the date format have anything to do with the count I am getting?

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    Looking at the above formulas I would think you need to change the date to include April.......


    Denny

    [ This Message was edited by: kinkyparamour on 2002-04-03 12:09 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I did change the dates in the above formulas to be >3/31/02 and less than 5/1/02, and also tried other dates, but I always get a count of 1.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 12:27, TClark14 wrote:
    I did change the dates in the above formulas to be >3/31/02 and less than 5/1/02, and also tried other dates, but I always get a count of 1.
    You could also put in criteria cells, say,

    in E1: 4/1/02 and

    in E2: =EOMONTH(E1,0) or just 4/30/02

    in oder to specify the whole month April (of 2002) as criterion, and use the formula:

    =COUNTIF(A1:A10,">="&E1)-COUNTIF(A1:A10,">"&E2)

    That you get an unexpected count could be due to the formatting of the date range.

    Check the dates as follows:

    in B1 enter: =ISNUMBER(A1) and copy down. You should get only TRUE's in B. If not, your dates are probably "text", not true dates.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •