up vote 4 down vote favorite
2
share [g+] share [fb]

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?

link|improve this question
84% accept rate
Where did this "TimeOccurred" column come from, and where did "Device Model" go? – wfaulk Sep 29 '09 at 19:13
I think he's trying a SELECT Date, COUNT([Device Model]) AS TimeOccured GROUP BY Date – Joey Sep 29 '09 at 19:17
I would agree, but then his two data sets don't match. – wfaulk Sep 29 '09 at 19:43
@wfaulk TimeOccured is something I came up with to see how many times the row occurs for a certain date. – AngryHacker Sep 29 '09 at 21:50
@Rossel Yes, that is exactly right. I supposed I could just throw the data into the database and run a query against it. – AngryHacker Sep 29 '09 at 21:51
feedback

3 Answers

up vote 4 down vote accepted

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.

link|improve this answer
feedback
up vote 4 down vote

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.

link|improve this answer
Add a column that is of type date with value =FLOOR(A2,1) .Then Insert -> Pivot Table. Then drag the date column on the right into the Rows box, and also drag it into values box. Done. – David Freitas Apr 18 '11 at 14:28
feedback
up vote 1 down vote

This site explains how grouping can be easily done through a pivot table.

To group the items in a Date field

  • Right-click the Date field button.
  • Choose Group and Show Detail | Group
  • In the Grouping dialog box, select one or more options from the 'By' list.
  • To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes
  • Click OK to close the dialog box.
link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.