Counting Occurences of Date (from column which is in MM/DD/YY T:TT format)

**maxc** · 10-19-2011, 05:53 PM

Hi I posted here yesterday and got some great effective responses, so I'm back for more.

Still have the same spreadsheet. It has a bunch of data in column D which is dates.

I'm trying to quickly count the occurrences of those dates

ie.

D8 10/1/11 7:54 AM
D9 10/1/11 10:52AM
D10 10/2/11 3:57PM

I need a formula that can count how many 10/1 and 10/2 occurrences there are

I've tried

=COUNTIF(D8:D10, "10/1/11")

but that doesn't work

Please advise

**gmcconnell** · 10-19-2011, 06:04 PM

This may not be the best way but I created a column starting in E8 with the formula =TRUNC(D8). I copied this formula down to E10 and used the formula =COUNTIF(E8:E10,40817).

It works but I would imagine that somebody on here knows how to accomplish this task without the use of an extra column.

The reason that your formula did not work is due to formatting I believe. Your data is dates and you are trying to count it if it is text.

**tigeravatar** · 10-19-2011, 06:04 PM

maxc,

The problem is that Excel doesn't read dates like a human does. If you were to format a cell containing a date to general it would be a number. Each whole number is a new day. If a date has a time value, then the number gets a decimal to represent the fraction of the day that has passed. So, for example, 10/1/11 7:54 AM = 40817.3291666667

Knowing this, we can convert the dates to integers and check to see if the integer is equal to the desired date. It would be best to use sumproduct for something like this. Also, becaue we need to convert a text string (the date we're looking for) into its corresponding excel date number, we'll need to use the Value function:
=SUMPRODUCT(--(INT(D8:D10)=VALUE("10/1/11")))

**maxc** · 10-19-2011, 06:15 PM

Thanks again tigeravatar. That formula is working for me.

I kind of understand what you mean about excel converting the dates to numbers. So to excel, 10/1/11 is the 40817th day and 7:54 is exactly .3291666667 through the day.

I'm still not that familiar with excel functions like SUMPRODUCT, but hopefully I can learn.

Very helpful.

**maxc** · 10-19-2011, 06:20 PM

Thanks for trying to help gmcconnell. Not sure how to use that formula though...

**gmcconnell** · 10-20-2011, 02:42 PM

The TRUNC formula just truncates the number so that the decimals are removed. That way it can be compared to 40817 which is the day you are looking for.