How do I count records by date in Excel?

Question

I have a month of data in an Excel spreadsheet. The date and time are in the same column. It's like 2007-01-15 12:05. I want to count the number of records for each day. I did it by hand and it took me a couple of hours. Next month, I want to take a short cut like using the autofilter or the count formula. I tried the autofilter and it works fine for some days and not other days. I told the autofilter to show everything that contains *01-15* and it didn't show anything that happened on the fifteenth of January. When I used the autofilter to show everything that contains *01-31* it showed everything on January 31st just fine. What could be going wrong? What should I do?

Answer 1

Create a new column right next to it(we'll call it column B). Type the following formula "=Left(A1,10)" A1 being the data to be stripped and 10 being the number of digits you want to see. Drag that formula all the way down to the bottom. Then do a subtotal column B with a "count" in each change in column B.

Answer 2

Easiest way would be to format the date/time field to just date and click Data>Subtotal to count the records.

If you want to keep that date/time field, add a new column and copy the date/time column to it and perform the steps above.

Answer 3

Don't forget date and time is expressed in decimals. 12 noon on 15th is expressed in 12.5. Filter using less than and more than expression. eg <16 for 1-15, >=16 for above 16th. We do the formula surgery when this does not get what you want.

Answer 4

Let's say your dates are in col. A starting at A1. Then in B1 enter the following formula:

=DAY(A1)

Copy and paste the formula from B2 down to the end of your data in col. A. Let's say it is a total of 100 cells, B1 through B100.

Now in cells C1 to C31 enter the numbers 1 to 31. You can enter 1 in C1, 2 in C2 and drag down to fill the remaining cells with 3 through 31.

Then in cell D1 enter the following formula:

=COUNTIF(B1:B100,C1) Note: in place of B100 would be the actual last row of your data.

Copy and paste the formula in cell D1 to cells D2 through D31.

Now cells C1 through C31 will have the days of the month 1 to 31, and next to each day in cells D1 through D31 will be a count of the number of records for that day.

Answer 5

you need to use the more desirable clear out to extract the unique values from a column of archives and paste them to a sparkling region. then you definately can use the ROWS function to count quantity the kind of things interior the hot selection. determine that the 1st row interior the column has a column header. on the archives menu, factor to clear out, and then click more desirable clear out. interior the more desirable clear out verbal substitute field, click replica to a various region. If the selection which you're counting isn't already chosen, delete any training interior the checklist selection field and then click the column (or go with the selection) that consists of your archives. interior the replica to field, delete any training interior the field or click interior the field, and then click a sparkling column the place you go with to repeat the unique values. go with the unique documents purely verify field, and click ok. the unique values from the chosen selection are copied to the hot column. interior the sparkling cellular under the final cellular interior the selection, enter the ROWS function. Use the selection of unique values which you purely copied via fact the argument. as an occasion, if the selection of unique values is B1:B45, then enter: =ROWS(B1:B45)