Share via

Pivot Table Date problem

Anonymous
2025-01-13T09:35:08+00:00

I have a column with a formula that determines the expired date in the raw data =IF(ISBLANK(I1), "", LET(d, DATE(5+YEAR(I1596), MONTH(E1), DAY(E1)), IF(d <= EDATE(I, 5*12), EDATE(d, 12), d))). When I create a pivot table, it does not allow grouping as a date.

Microsoft 365 and Office | Excel | For business | Android

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 460.7K Reputation points MVP Volunteer Moderator
    2025-01-13T10:27:50+00:00

    That is because the date column contains at least one cell in which the formula returns "", which is not a date value.

    You can only group dates if ALL values are valid dates.

    As a workaround, you could make the formula return 0 instead of "" if column I is blank.

    Refresh the pivot table. then group the date field. You can specify the start date when you group.

    0 comments