Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count individual days (97 SR2)

    I have a giant spreadsheet which contains a date on every row. The date is always in Column A, and repeats several times until the next date, which also repeats several times. Basically, there may be as many as 100 items entered per day, each dated as such.

    The sheet covers the year to date. In order to perform some calculations, I need to be able to count the number of individual days covered. These dates never contain a weekend day, so a simple (first date - second date) formula won't work.

    Does anyone have any idea how I might go about counting the number of individual days covered by this spreadsheet? I'm simply looking for the number of individual date criteria which exist in the sheet.

    Thanks for all your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count individual days (97 SR2)

    You could use a pivot table, or the function below:

    <pre>Public Function UniqueDays(oRangeToCount As Range)
    Dim lCnt As Long, I As Long
    I = 1
    lCnt = 1
    With Worksheets("Sheet1").Range("A1")
    Do While .Offset(I, 0).Value <> ""
    If .Offset(I - 1, 0).Value <> .Offset(I, 0) Then lCnt = lCnt + 1
    I = I + 1
    Loop
    End With
    UniqueDays = lCnt
    End Function
    </pre>


    Pass the largest range that might contain dates (A:A for the entire column A) as the parameter. The parameter is not used in the function, but will get the function to recalculate if anything in the range is changed.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count individual days (97 SR2)

    Chip Pearson may also have something to fit the bill; scroll down his duplicates page a bit.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count individual days (97 SR2)

    Assuming Column A has a header and the dates start in A2 and are sorted you could use an array formula. The following formula, provided it is placed in a column other than A, should work :

    =SUM(IF(A2:A65536=OFFSET(A2:A65536,-1,0),0,1))-1

    You should enter that with Ctrl-Shift-Enter, so that Excel encloses it in brace brackets ({}).

    Andrew C

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count individual days (97 SR2)

    Array-enter, using control+shift+enter:

    =SUM(IF(LEN(A2:A8),1/COUNTIF(A2:A8,A2:A8)))

    where A2:A8 houses the dates to count.

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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