Cleaning trailing zeros (.00) from dates so the Group function works in a pivot table

Question

Using Excel 2010, I am getting a

Cannot group that selection

error when attempting to group dates. The date data came to me in an .xls file with this format: 2016-01-07 5:00 PM in a single column.

Using Text to Columns feature, I peeled off the dates to a separate column. The dates display correctly, however, when inserted into a Pivot Table and I attempt to group the dates, I get the referenced error. If I reformat the data to numbers, it is not clean data. The date 2016-01-23 converts to 42392.00.

My thinking is these trailing .00s are creating unclean data fields that Excel's pivot feature cannot group. There are thousands of lines of this data and I cannot possible convert each by hand.

I've tried removing the zeros using the decimal reduction tool and they visually disappear but when recreating a pivot table with the new formatting, the error message continues. Frustrated, I need some assistance please.

Answer 1

In Excel, dates are just whole numbers - as you've identified, everything after the decimal point are your hours, minutes and seconds.

Just add a helper column to trim off the extra digits. If your data starts in column B2, add a column C starting with the following formula, filled down to the bottom of the sheet: =FLOOR(B2,0)

This will round your data down, trimming off any hours which may not be what you're looking for. Alternatively, consider the CEILING or ROUND instead of FLOOR to either round them all up or to the nearest day.