Group Dates and Numeric Bands in a LibreOffice Calc Pivot Table

Grouping summarizes pivot categories without changing the original records. Use both year and month when a date field crosses years; otherwise the same month name can combine different reporting periods. For numeric bands, decide the start, end and interval before trusting the labels.

Source data and scope

Use desktop Calc with the standard menu bar and an ODS file. The procedures follow Calc Guide 26.2 and current Help, directly checked as LibreOffice 26.8 Help on October 19, 2026. These are expected outcomes backed by independent calculations and documentation; no native pivot run is claimed.

Name a sheet Events. Enter this table in A1:D8. For the dates, enter actual DATE formulas such as =DATE(2025;1;5) for 2025-01-05, then apply a date number format. This avoids relying on CSV date detection.

ID	Event date	Score	Weight
E1	2025-01-05	0	10
E2	2025-01-31	99	20
E3	2025-02-01	100	30
E4	2026-01-01	199	40
E5	2026-01-15	200	50
E6	2026-02-01	299	60
E7	2026-02-28	300	70

Enter =ISNUMBER(B2) in a spare cell and repeat for the other six dates; every result must be TRUE. Formatting text to look like a date is insufficient. The seven weights sum to 280. Keep this source free of subtotal rows.

1. Build a date pivot, then group its labels

Select Events.A1:D8 and choose Insert → Pivot Table, using Current selection. Put Event date in Row Fields and Weight in Data Fields. Double-click the data-field button to confirm Sum, then send the pivot to a new sheet.

Select one date item inside the resulting pivot, not its title or a grand-total cell. Choose Data → Group and Outline → Group. In the date grouping dialog, use the full automatic start/end range and select Intervals with Years and Months. Confirm. The grouping Help explains why the selected category determines the grouping dialog.

Check the pivot layout after grouping. It must preserve year as an outer category and month within each year. If necessary, reopen Data → Pivot Table → Insert or Edit and arrange the generated year field before the month/date field in Row Fields. Do not accept a report containing only January and February with no year distinction.

Year	Month	Expected weight sum
2025	January	30
2025	February	30
2026	January	90
2026	February	130
All years	Grand total	280

The year totals are 60 and 220. As a failure check, January without year separation would total 120. That value is mathematically valid for all Januaries, but answers a different question. The Calc pivot guide covers calendar intervals and grouping category items.

2. Build a separate numeric-band pivot

Return to Events and create another pivot from the same A1:D8 source. Put Score in Row Fields and Weight in Data Fields, again using Sum. Use a new destination sheet so this layout does not replace the date report.

Select a numeric score item in the pivot and choose Data → Group and Outline → Group. Set Start → Manually at to 0, End → Manually at to 400, and Group by to 100. The Grouping dialog reference documents these controls.

Read the following table as a membership specification, rather than a promise of exact localized label typography:

Band starting at	Fixture scores belonging to it	Expected sum
0	0, 99	30
100	100, 199	70
200	200, 299	110
300	300	70
Grand total	All seven records	280

The boundary scores 100, 200 and 300 each enter the band starting with that number. This fixture deliberately puts the configured end beyond the largest source value. If End were automatically taken as 300, terminal-boundary handling could give a different last bucket from the one intended here.

3. Change a boundary value and refresh both reports

Change Events.C4, the Score for E3, from 100 to 99. Click in the numeric pivot and choose Data → Pivot Table → Refresh. Its first two sums should become 60 and 40; the other bands remain 110 and 70. The grand total stays 280 because only category membership changed.

Refresh the date pivot as well. All its totals remain unchanged because neither the dates nor weights changed. This is a stronger check than accepting an unchanged grand total alone.

For an additional endpoint experiment in a disposable copy, change E7’s score from 300 to 400, then refresh the numeric pivot. It remains in the final configured band. Change it to 401 and refresh again: its weight 70 belongs above the configured end. LibreOffice’s 26.8.1.1 numeric grouping implementation includes the exact end in the preceding band when it would otherwise stand alone; a value greater than End is outside the interval range. This is a source-code check, not a native observation.

Restore E3 to 100 and E7 to 300, refresh and reconcile the original band table. Save, close and reopen the ODS, then check both layouts and totals.

Troubleshooting and recovery

• Text groups appear instead of a date dialog: verify the source dates are numeric and date-formatted, then rebuild the test pivot from clean source data.
• The command is unavailable: select an actual row/column category item inside the pivot; a source cell or aggregated value is the wrong target.
• A new record is missing: refresh does not prove an appended row lies inside the pivot source. Follow repairing a pivot’s source range.
• Only totals look right: compare the listed members and both year/month levels. Wrong buckets can preserve the grand total.

To remove a grouping, select a grouped category and choose Data → Group and Outline → Ungroup. Reopen the layout and inspect the remaining fields before regrouping. Keep the raw Events sheet untouched so that a confusing layout can be rebuilt without reverse-engineering grouped labels.