Count individual days (97 SR2)

[Kel]

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!

[LegareColeman]

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.

[JohnBF]

Chip Pearson may also have something to fit the bill; scroll down his duplicates page a bit.

[Andrew Cronnolly]

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

[Aladin Akyurek]

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