+ Reply to Thread
Results 1 to 3 of 3

Counting Dates-number of cells

  1. #1
    MJMP
    Guest

    Counting Dates-number of cells

    In a column, there are entries with dates (in MM/DD/YYYY format). How can I
    count the number of cells that have dates in them? I do not want to input a
    criteria for the date. I just would like the number of cells that have any
    date in it.

    Thanks!

  2. #2
    Bob Phillips
    Guest

    re: Counting Dates-number of cells

    Tricky, because dates in Excel are just number of days since 01//01/1900, so
    it is difficult to distinguish a date from any other positive integer number

    If you set limits on the dates, say since 1970, and up until 2010, you could
    use

    =SUMPRODUCT(--(A1:A1000>=--"1970-01-01"),--(A1:A1000<=--"2011-01-01"))

    --
    HTH

    Bob Phillips

    "MJMP" <[email protected]> wrote in message
    news:[email protected]...
    > In a column, there are entries with dates (in MM/DD/YYYY format). How can

    I
    > count the number of cells that have dates in them? I do not want to input

    a
    > criteria for the date. I just would like the number of cells that have

    any
    > date in it.
    >
    > Thanks!




  3. #3
    Peo Sjoblom
    Guest

    re: Counting Dates-number of cells

    You can't really check if it is a date since dates are plain numbers
    formatted as dates with date 0 on jAN 0 1900, however you can check for
    numbers in the range
    (there is a function called CELL("format",cell) but it is not reliable since
    if the format changes it will change as well

    =SUMPRODUCT(--(ISNUMBER(Range))

    will count cells with numbers, if they are text you can use
    ISTEXT instead of ISNUMBER

    Regards,

    Peo Sjoblom


    "MJMP" wrote:

    > In a column, there are entries with dates (in MM/DD/YYYY format). How can I
    > count the number of cells that have dates in them? I do not want to input a
    > criteria for the date. I just would like the number of cells that have any
    > date in it.
    >
    > Thanks!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1