grouping by date in excel and removing time in a pivot table

Question

My data looks like this:

count Added_Date

1 8/26/09 3:46 PM
2 8/21/09 6:50 PM
3 8/21/09 3:04 PM
4 8/21/09 3:21 PM
5 5/1/09  6:56 AM
6 5/1/09  8:12 AM
7 5/1/09  8:00 AM
8 5/1/09  8:18 AM
9 5/1/09  8:58 AM
10 5/1/09 8:58 AM
11 5/1/09 9:06 AM
12 5/1/09 9:44 AM
13 5/1/09 9:50 AM
14 5/1/09 11:17 AM
15 5/1/09 11:27 AM
16 5/1/09 11:29 AM
17 5/1/09 11:39 AM
18 5/1/09 12:10 PM
19 5/1/09 12:33 PM

When I do a pivot table, I cannot get it to sum by day, it breaks it up by minute. I've even tried parsing the field, but the time always creates an issue.

How to I get my pivot table to give me a count by day and ignore the time stamp?

Answer 1

If you have any cells in your Added Date column that did not parse to proper Excel times, then this will prevent the Group By function in a pivot table from working correctly.

Make sure you are entering your dates according to your locale settings, otherwise they will not parse as dates.

eg. I type in 8/26/09 3:46 PM (US date standard) in to my Excel installation (Australian locale) and it stays at text instead of parsing to a proper date.

Answer 2

Add a Column to convert the time stamp column to date column using "=int(Cell address)" and use this column in pivot table for your analysis.

Answer 3

You may need to highlight the cells, click on "Format Cells..." and change the number format to Date instead. This will convert all the timestamp info inside the cell to show only the dates, not the time.