Exceljet

Quick, clean, and to the point

Count unique dates ignore time

Excel formula: Count unique dates ignore time
Generic formula 
=COUNT(UNIQUE(INT(data)))
Summary 

To count unique dates and ignore time values you can use a formula based on the UNIQUEINT, and COUNT functions. In the example shown, the formula in F4 is:

=COUNT(UNIQUE(INT(data)))

where data is the named range B5:B16, and each timestamp includes both a date and time. See below for the formulas used to generate the summary table in E7:F9.

Explanation 

In this example, the goal is to count the unique dates in a range of timestamps (i.e. dates that contain dates and times). In addition, we also want to create the table of results seen in E7:F9. For convenience, data is the named range B5:B16.

Basic count

To get a count of individual dates that occur in B5:B16, ignoring time values, the formula in F4 is:

=COUNT(UNIQUE(INT(data)))

Working from the inside out, the INT function is used to remove the time values from the timestamps like this:

INT(data) // remove time

This works because Excel dates are just serial numbers and Excel times are fractional dates that appear as decimal values. The INT function simply removes the decimal portion of the date, leaving the serial number intact. Because we are giving the INT function 12 separate values, INT returns 12 results in an array like this:

{44600;44600;44600;44600;44604;44604;44604;44635;44635;44635;44635;44635}

Each serial number in this array represents a date in data. Next, the UNIQUE function is used to remove duplicate dates. The two functions work together like this:

UNIQUE(INT(data)) // returns {44600;44604;44635}

Because there are 3 unique dates, UNIQUE returns 3 serial numbers in an array like this:

{44600;44604;44635}

This array is delivered directly to the COUNT function:

=COUNT({44600;44604;44635}) // returns 3

and the COUNT function (which counts numbers only) returns 3 as a final result.

Summary table in two parts

An easy way to create the summary table seen in the worksheet is to use two formulas. To list the 3 unique dates starting in cell D7, you can use:

=UNIQUE(INT(data)) // unique dates

To count how many times each date occurs in the data, you can enter this formula in cell F7 and copy it down:

=SUM(--(INT(data)=K7))

Note: we can't use the COUNTIF function in this case (which would be easier) because we don't have the dates without times in an actual range. Instead, the dates exist in an array returned by the INT function. COUNTIF requires a range for the range argument.

All-in-one summary table

To create an all-in-one summary table that lists the unique dates along with a count for each date, you can use an advanced formula like this:

=LET(d,INT(data),u,UNIQUE(d),HSTACK(u,SCAN(0,u,LAMBDA(a,v,SUM(--(v=d))))))

All-in-one summary table formula

The LET function is used to assign intermediate results to named variables. First, we use INT to strip times from dates and assign the result to d. Then we feed d into UNIQUE (to get unique dates) and assign the result to u. Next, we use the HSTACK function to combine two arrays. Array1 is u (the unique dates), and array2 is generated with the SCAN function:

SCAN(0,u,LAMBDA(a,v,SUM(--(v=d))))

With an initial value of zero, SCAN iterates through each date in u and runs a custom LAMBDA function to count how many times the date appears in d:

LAMBDA(a,v,SUM(--(v=d))

The count is generated with boolean logic and the SUM function. The result from SCAN is an array with 3 counts:

{4;3;5}

This array is returned to HSTACK as array2. HSTACK joins array1 and array2 together horizontally, and returns the 2-column array as a final result.

Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.