Excel 資料透視表無法按日期組合

Tested in: The serial number and text-versus-date checks below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click Group in Excel this session.

The broader problem of text that looks like a date is in dates stored as text. This page is about the error you see when a pivot will not group that column.

What Group needs

In Excel, right-click a date in the pivot and choose Group, then pick Months, Quarters, or Years. That command works on true date values — numbers that Excel formats as dates. =DATE(2024,1,8) is the serial 45299. If even one row in the source is not a date, grouping is refused for the whole field.

Blanks can also block grouping if they sit inside the source range. A leftover “n/a” or a note in the date column will do the same. Filters do not help: grouping looks at the field in the source, not only the rows you are displaying.

How to test the source column

Pick a suspicious cell, for example A2, and check it:

=ISNUMBER(A2)
=ISTEXT(A2)

A real date should be TRUE on ISNUMBER and FALSE on ISTEXT. In the test workbook I stored the characters 2024-01-08 as text in L4. On 12 November 2026, =ISTEXT(L4) was TRUE and =ISNUMBER(L4) was FALSE. That cell would break grouping if it were in a pivot source.

Google Sheets also has ISDATE. On this text cell, =ISDATE(L4) returned TRUE, because Sheets can recognize the string as date-like even though it is still text. Do not use ISDATE alone as a “this will group in Excel” test. ISNUMBER is the one that matches how Excel treats dates.

To convert a text date:

=DATEVALUE(L4)

That formula returned 45299, and =TEXT(DATE(2024,1,8),"yyyy-mm-dd") returned 2024-01-08. Copy the converted values and paste them as values over the source, or add a helper column of DATEVALUE results and build the pivot on that column. If DATEVALUE returns #VALUE!, the text is not in a date order your locale understands; the cleanup steps in dates stored as text cover those formats.

After you convert, refresh or rebuild

A pivot that was built on the text column will not start grouping just because you fixed the cells. Refresh the pivot so the cache picks up the numbers, then try Group again. If Group is still disabled, remove the date field from the pivot and drag it back, or create a new pivot on the cleaned range.

If the field was already grouped (for example by month) and you added a non-date row, ungroup first, fix the source, refresh, then group again.

In Google Sheets, there is often no equivalent Group dialog. Put a month start in a helper column:

=DATE(YEAR(A2),MONTH(A2),1)

and use that helper in the pivot’s Rows. The helper will also fail if A2 is unreadable text, so run the same ISNUMBER / DATEVALUE checks first.

Once dates group, a chart of those months can still plot serial numbers if the axis is formatted as a number; that is a separate fix, in Excel chart dates showing as numbers.