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 9630Somehow, I'd like to group it by the date, so it would look like this:
Date TimeOccured
29-Sep-2009 3
28-Sep-2009 3Is this possible in Excel?
54 Answers
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.
1This 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.
1This 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.
Assuming all your data is in A1, Select the data and convert text to columns. Use delimiter as space. Now you've got the date in A1.
In B1, copy the unique dates, in this case 3
In C1 enter the formula: =COUNTIF(A1:A11,$B$1:$B$3)
Use the cell handle (bottom right edge) and drag to the next two cells.
Output:
9/29/2009 4
9/28/2009 3
9/27/2009 4
