Pivot Table Issues

Calculate Daily Production Totals by Shift in Excel Pivot Tables

Amos Gikunda Oct 22, 2026 868 views

Question details

The user needs to classify production records into specific shifts (such as A, B, and C) and correctly attribute overnight shift data to the preceding day's total before analyzing it with a PivotTable.

Product: Excel
Device & OS: not provided
Scenario: Generating daily production reports where work shifts span across midnight, requiring custom date attribution.
Observed behavior: Standard PivotTable grouping relies on rigid calendar dates and hours, which splits overnight shift data into two different dates instead of keeping it unified under a single production date.

Before you start

Ensure your source data contains a complete timestamp column (date and time combined) and that your data range is formatted as an Excel Table so new formulas apply automatically.

Solution 1Recommended

Use Helper Columns for Shift and Production Date in a PivotTable

This method ensures accurate grouping by adding custom formulas to correctly assign overnight records to the starting date of the shift, allowing the PivotTable to group data perfectly.

Standard Excel PivotTables group by strict calendar dates, which inherently breaks overnight shifts (e.g., 10 PM to 6 AM) into two different calendar days. By creating 'Production Date' and 'Shift' helper columns in your source data, you bypass this limitation and maintain control over how the records are categorized.

Add a Production Date column

Create a new column next to your timestamp data. Use a formula like =INT(A2 - TIME(6,0,0)) (assuming 6 AM is the daily shift reset) to push overnight hours back to the previous calendar day's date.

Add a Shift Classification column

Create a second column and use an IF or IFS formula based on the time portion of your timestamp. For example: =IF(AND(MOD(A2,1)>=TIME(6,0,0), MOD(A2,1)<TIME(14,0,0)), "Shift A", IF(AND(MOD(A2,1)>=TIME(14,0,0), MOD(A2,1)<TIME(22,0,0)), "Shift B", "Shift C")).

Create the PivotTable

Select your newly updated data range (including the headers), go to the 'Insert' tab on the ribbon, click 'PivotTable', and choose to place it on a New Worksheet.

Configure PivotTable fields

In the PivotTable Fields pane, drag the new 'Production Date' and 'Shift' columns into the Rows area. Then, drag your production quantity or output field into the Values area to calculate the total daily shift outputs.

Use Helper Columns for Shift and Production Date in a PivotTable

Automatic Formula Updating: If you format your source data as an Excel Table by pressing Ctrl+T before adding the new columns, your calculated formulas will automatically apply to any new production records added to the bottom of the list.

Solution 2

Use SUMIFS or COUNTIFS for Dashboard Summaries

If you prefer not to use PivotTables, you can build a static summary table utilizing multiple-criteria functions alongside your calculated columns.

Easily Calculate Shift Totals with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools, including advanced PivotTables and robust logical functions, making it simple to organize complex shift schedules and overnight production totals.

1. Open Data: Launch WPS Spreadsheet and open your production log data.
2. Add Custom Columns: Insert 'Shift' and 'Production Date' columns utilizing logical IF and INT functions to adjust for overnight boundaries.
3. Insert PivotTable: Highlight the dataset, navigate to the Insert tab, and click PivotTable.
4. Summarize Shifts: Drag 'Production Date' and 'Shift' to the Rows area, and your production metrics to the Values area to instantly view shift totals.

Advanced PivotTable functionality for easy and flexible data summarizationSeamless compatibility with Microsoft Excel (.xlsx) formats and formulasLightweight and fast performance, ideal for handling large production datasetsBuilt-in advanced logical functions like IFS and SUMIFS for custom shift logic

microsoft office alternative - wps office

Frequently Asked Questions

How do I extract just the time from a date-and-time timestamp cell?

You can extract the time from a datetime cell by using the MOD function. If your timestamp is in cell A2, use =MOD(A2, 1). This returns the fractional part of the number, which represents the time of day in Excel.

How do I handle overnight shifts so they count for the previous day?

You need to deduct the shift's end time (or shift boundary time) from the actual date-time before formatting it as a date. For example, using =INT(A2 - TIME(6,0,0)) shifts any record generated before 6:00 AM backwards into the previous calendar day.

Can I group PivotTable data by custom time periods without adding helper columns?

Excel's native PivotTable grouping only allows for standard intervals like hours, days, months, or quarters. To group by custom intervals like specific 8-hour shift schedules, adding helper columns to the source data is strictly required.

Excel & Data Problems