formatting date for pivot table

[Seede]

hello
the data in my spreadsheet is with datetime. when i create a pivot table, it
creats multiple rows for data with same date.
when i tried to change the format, it did not help
how can i create a pivot table that does not create multiple rows for data
with sme date when my souce excel data is in datetime.

 

[Dave Peterson]

After you create the pivottable, you can:

rightclick on the date/time header.
Choose Group and Show Detail
Then choose Group
and group by Days
(unselect any other options)



Or you could do this:
Insert another column with just the date:
=int(a2)
and format it as a date.

And then pivot on that extra helper column.

 

[Shane Devenshire]

Hi,

Of course another alternative is to create a formula in a blank column of
the data range which only returns that date. =INT(A1) for example. Then
use this as your row field.

Another option is to select the Date column in you raw data and choose Data,
Text to Columns, Fixed Width, Next, make sure the line in the preview pane is
in the correct place to the right of the date portion. Click Next, select
the time column in the preview pane and select Do not import (Skip). Click
Finish. This will eliminate the times leaving only the dates.