Count Unique Days

I found this fun challenge on the Mr. Excel forums.

You can count unique items in a list with an array formula described on Chip’s site. I use a variant of it below.

The problem is that if you have a list of dates and times and you only want a count of the unique days. To disregard the times, try the below formula.


13 thoughts on “Count Unique Days

  1. Interesting how different people approach a problem. Array formulas are awesome, as long as you don’t plan on sharing the workbook… 99.9% of Excel users are totally mystified by them. I wrote a function that counts the unique values in an array. It was easy enough to create a function that converts the range to an array of dates then pass it to this function. Here it is!

    Option Explicit
    Option Base 1

    Function CountDays(rng As Range)
    ‘Counts the number of unique days in a range
    Dim i As Long, n As Long, u As Long
    Dim dVals() As Long

    ReDim dVals(65536)

    n = Int(rng.count)
    i = 1
    ‘Search in each (valid) cell in the range

    u = 0
    For i = 1 To n
      ‘If the cell is a date, add it to a new array
      If IsDate(rng(i)) Then
        u = u + 1
        dVals(u) = Int(rng(i))
      End If
    Next i

    If u > 0 Then
      ‘Pass the array of dates to a function that counts
      ‘the number of unique values in an array
      ReDim Preserve dVals(u)
      CountDays = CountUnique(dVals)
      ‘Return an error value if there are *no* dates
      CountDays = “#N/A “
    End If

    End Function

    Function CountUnique(Arr As Variant) As Long
    ‘Counts the number of unique values in an array
    ‘Assumes Option Base 1
    Dim i As Long, n As Long, k As Long, u As Long
    Dim UniqueVals() As Variant
    Dim IsUnique As Boolean
    n = UBound(Arr)
    ReDim UniqueVals(n)
    u = 1

    UniqueVals(1) = Arr(1)
    For i = 1 To n
      k = 1
      IsUnique = False
      Do While k < = u
        If Arr(i) <> UniqueVals(k) Then
           IsUnique = True
           k = k + 1
           IsUnique = False
           Exit Do
        End If
      If IsUnique Then
        u = u + 1
        UniqueVals(u) = Arr(i)
      End If
    Next i
    CountUnique = u
    End Function

  2. How about this one

    Function CountUniqueDays(rRng As Range) As Long

        Dim cDays As Collection
        Dim rCell As Range
        Set cDays = New Collection
        On Error Resume Next
            For Each rCell In rRng.Cells
                If IsDate(rCell.Value) Then
                    cDays.Add rCell.Value, CStr(CLng(rCell.Value))
                End If
            Next rCell
        On Error GoTo 0
        CountUniqueDays = cDays.Count

    End Function

  3. Dick, that’s nice! You did it in about 1/4 of the lines of code. I’d never even heard of a “Collection” before. I have a feeling knowing about this would have saved me a lot of time coding. For instance, I never would have had to write that CountUnique function…

    The collection object might be a good topic for a future post!

  4. “The collection object might be a good topic for a future post!”

    Geez, why didn’t I think of that. Thanks, Matt.

  5. Do you have any suggestions as to expanding this functions with a condition?

    I am trying to count unique values/dates per period, where I have the dates in one column and the periods(numeric representation of the month) in another column (if I can discard this latter column that would be even better).

    Thanks in advance,


  6. hi this is not a comment it looks more as a question:-)
    for Excel guru’s the know already for what I’m looking for;right the number of weekdays(D7)&the 13th
    my result =0
    plrese help me

  7. Matt,

    Converting to a Long with CLng rounds decimals. Since the time portion of the date value is a decimal, you can easily end up with different dates being counted as the same (4pm of one day is the same as 4am the next day after rounding).

    To fix it, use Int to truncate the decimal instead:

    If IsDate(rCell.Value) Then
    cDays.Add rCell.Value, CStr(Int(rCell.Value))
    End If

  8. I used Dick’s original formula that started this thread and received the following error:

    A value used in the formula is of the wrong data type set.

    When I show the calculate steps used in evaluating the formula, it indicates that the range of data I selected will result in an error.

    I formatted the entire range of cells as ‘Date’ > ‘3/14/01 13:30’ to match the example, but the error is still returned.

    I simply replaced the example range with my range.
    Here is the exact formula text:

    Am I missing a step? Thanks.

  9. Matthew: Make sure you’re entering with Control+Shift+Enter as it is an array formula. If you are, then format all of your dates as ‘General’. This will make them look like numbers and any text you have in the list will stand out (because it will still look like a date).

  10. Hi, I was trying to use this formula to count unique days but I was not able. Could you please help me?
    I have a column F6 to F36 with short date and I have the following dates long the column: 3/10, 3/11, 3/12, 3/13. I would like to count how many unique days I have. I tried the above formula but I got the “#VALUE!”.. Any idea?
    Thanks in advance!!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.