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?

Row Labels

2/9/2018 12:41

2/9/2018 12:43

2/9/2018 12:45

2/9/2018 12:52

2/9/2018 12:53

2/9/2018 12:55

2/9/2018 12:56

2/9/2018 15:49

2/12/2018 10:04

2/15/2018 3:29

2/15/2018 12:09

<colgroup><col></colgroup><tbody>
</tbody>

 

[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?

Row Labels

2/9/2018 12:41

2/9/2018 12:43

2/9/2018 12:45

2/9/2018 12:52

2/9/2018 12:53

2/9/2018 12:55

2/9/2018 12:56

2/9/2018 15:49

2/12/2018 10:04

2/15/2018 3:29

2/15/2018 12:09

<tbody>
</tbody>

 

[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!