How to group by date in excel?

I have a sheet in Excel 2007 that looks like this:

Date/Time               Device Model
29-Sep-2009 06:50:42 PM   9000
29-Sep-2009 06:02:16 PM   8900
29-Sep-2009 05:40:10 PM   8330
29-Sep-2009 05:38:35 PM   8310
28-Sep-2009 05:19:29 PM   8900
28-Sep-2009 04:45:58 PM   8230
28-Sep-2009 04:26:24 PM   9000
27-Sep-2009 04:25:07 PM   9530
27-Sep-2009 04:19:09 PM   8520
27-Sep-2009 04:05:51 PM   9530
27-Sep-2009 04:05:44 PM   9630

Somehow, I'd like to group it by the date, so it would look like this:

Date        TimeOccured
29-Sep-2009    3
28-Sep-2009    3

Is this possible in Excel?

Solution 1:

1) Put your data in Columns A and B.

2) In C2, put "= FLOOR(A2)". This strips out the time and leaves the date, since the unit for date/times stored as numbers is one day.

3) In Column D, put a list of the dates you're interested in. (You can do this, e.g, by putting "9/27/2009" in D2, putting "= D2 + 1" in D3, and copying D3 down until you have enough days.)

4) In E2, put "= COUNTIF(C:C, D2)", and copy down for each of your days in Column D. This will count how many entries in Column C match each entry in Column D.

Solution 2:

This type of data summarization is what Excel's pivot tables were designed for.

They automatically identify the unique dates and and count the occurences you are looking for.

There will be a bit of a learning curve if you haven't seen pivot tables before but they are well worth effort.

Leave a comment if you want me to write a full answer - I have to scoot right now.

With a simple count like this, you might want to try and figure it out for yourself in the meantime.

As moe37x3 said, you will have to add a column that strips out the time and leaves just the date.