0

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.

Number Filters in Raw Data

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.

Grouping Blanked out

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

Improve this question
1
  • "I have an option for number filters, which implies that the data is correct and excel knows what it is." No, it doesn't. Excel will give you this option in a mixed type field, and it will not treat the numbers stored as text correctly. I don't think you have proven that your data contains only numbers.
    – Alex M
    Apr 20, 2021 at 23:47

1 Answer 1

Reset to default
2

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)

data source

pivot table

Improve this answer
1
  • Thanks Steve, this is a really good and intuitive answer and I will end up using it. I'm not marking it as answered as it does not entirely solve my original problem with the Group Field not being active, this goes especially for dates.
    – ChP
    Dec 8, 2015 at 5:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .