Excel pivot table field grouping problems (numbers seen as text)

Question

I have a table which contains columns for activity names, start dates, and end dates. In a new column I calculate the time remaining for each activity with: INT(EndDate - Today()) The INT() is there just so that Excel ignores any time value.

When I click the drop down in the new column's header I have an option for number filters, which implies that the data is correct and excel knows what it is. The data is complete and correct with no errors and blanks.

I want to make a pivot table and group items by the remaining number of days. Furthermore I would like to group the number of remaining days by something like 5 or 10 days but I don't have that option. the Group Field option is blanked out. By looking at the pivot table filters it also does not seem that the pivot table recognizes the data as numbers.

I have done exactly this in other spreadsheets with different data but everything worked well. As far as I know I have done nothing different. I had similar experiences with grouping dates as well.

• I tried changing the data format between general and number in both the data table and pivot table
• I tried to approach the formulas in a different way (removing the INT())
• I created a brand new column with the calculation
• I created new pivot tables, refreshed old ones
• I encounter these issues in Excel 2010 and 2013.

Do you have any advice as to why the pivot table does this, and how I could fix it? Creating a new spreadsheet is the least preferable since there are lots of other tables in the spreadsheet.

Regards,

Charl

Answer 1

You could add another column to your table for remaining days mod 10. You can then hide the column if you don't want to have it visible The formula in the days remaining group column is

=FLOOR([@[days remaining]]/$E$2,1)