Count based on DATE Comparison

---

Suppose you have list of activities performed at different time of the day spanned for multiple days and you wish to count number of activities performed on a particular day. Consider the table below.

Image 1 – Master Table

Output expected is as follows:

Image 2 – Expected Result

Very simple job, isn’t it? Write formula in “Count of Activities” column as follows and have the result:

=COUNTIF(Table1[Date],[@Date])

Here Table1[Date] denotes the range of dates in the master table above and [@Date] denotes the specific date in current table.

However astonishingly results that we get as follows:

Image 3 – Result 1

This is not what we are looking for, aren’t we?

So where is the problem? Are we using COUNTIF incorrectly? NO we are not. Problem lies with the DATE.

Let’s expand the DATE in the Table 1 by changing the format of the cell to see what exactly we have here.

Image 4 – Table in Image 1 with Date in Expanded Format
Expected Result with Date in Expanded Format

The Time Factor in the DATE is the culprit why we are not getting desired output. Notice we are trying to compare with the DATE having Time Factor set as 12:00 AM. Obviously it won’t match.

DATE in Excel is stored in Julian format. Julian dates are sequential serial numbers in days and fractions denoting Date and Time together. Whenever we declare a DATE in Excel without explicitly specifying Time Factor, the Time Factor gets set to Midnight 12:00:00 AM. This Time Factor is the one which causes trouble while comparing DATE.

So how to deal with this issue?

We would need some modification in the formula that we used. First of all, let’s understand what are we looking for. By Count of Activities performed on 10-Oct, what we are looking for is the count of activities performed between midnight of 10-Oct (10/10/2012 12:00 AM) and midnight of 11-Oct (10/11/2012 12:00 AM). Thus we need to two conditions as follows:

-          Count of Activities performed after midnight of 10-Oct (10/10/2012 12:00 AM) AND

-          Count of Activities performed before midnight of 11-Oct (10/11/2012 12:00 AM)

Since we have more than one condition, COUNTIFS is a better choice and our formula will look like this:

=COUNTIFS(Table1[Date],">="&[@Date],Table1[Date],"<"&[@Date]+1)

And the Output is as expected:

Image 5 – Correct Output

---