Capture only the date in Pivot table

[p9j123]

I have a bunch of data, one of the column is received date on the following format.

mm/dd/yyy hh:mm

Now when I insert a pivot I am having the following. What do I need to do to group this by date only and do not consider the time?

[TABLE="class: grid, width: 182"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:41[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:43[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:45[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:52[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:53[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:55[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:56[/TD]
[/TR]
[TR]
[TD]2/9/2018 15:49[/TD]
[/TR]
[TR]
[TD]2/12/2018 10:04[/TD]
[/TR]
[TR]
[TD]2/15/2018 3:29[/TD]
[/TR]
[TR]
[TD]2/15/2018 12:09[/TD]
[/TR]
</tbody>[/TABLE]

 

[sachavez]

In your data set, add a helper column B:

Assuming your date/time column is "A", insert a new column B (name it "Date") and add the formula: =text(a2,"mm/dd/yy"), and copy that formula to the end of your dataset. Then refresh your pivot table and add the Date field.

I have a bunch of data, one of the column is received date on the following format.

mm/dd/yyy hh:mm

Now when I insert a pivot I am having the following. What do I need to do to group this by date only and do not consider the time?

[TABLE="class: grid, width: 182"]
<tbody>[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:41[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:43[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:45[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:52[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:53[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:55[/TD]
[/TR]
[TR]
[TD]2/9/2018 12:56[/TD]
[/TR]
[TR]
[TD]2/9/2018 15:49[/TD]
[/TR]
[TR]
[TD]2/12/2018 10:04[/TD]
[/TR]
[TR]
[TD]2/15/2018 3:29[/TD]
[/TR]
[TR]
[TD]2/15/2018 12:09[/TD]
[/TR]
</tbody>[/TABLE]

 

[sachavez]

Or you can use the "=int(a2)" formula, but make sure to format the cells as a date.

 

[MartyS]

Or you can just use it directly in a PIVOT table and use the GROUP function.

In the PivotTable, click on one of the dates to select a single cell.
Then right click to bring up the context-menu.
Select GROUP.

A dialog box will appear. You can then choose, Days, Months, Years, etc to suit.

If this doesn't work... it's because your date data isn't actually "dates" (numerical) - it would be text / variant and would need to be converted.

 

[p9j123]

Thank you all, @MartyS I used yours

 

[MartyS]

Glad it helped!