How to group by date in excel?

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.

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.

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.

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