Fill Missing Dates and Time Series Gaps

A missing date is a missing record, not simply an empty cell. If store A has one record on September 1 and another on September 3, a daily grid expects September 2 as well. This tool finds that absent time point and can create a new row for it. If September 3 already exists but its sales field is blank, that is an existing observation with an empty value. It is not counted as a newly missing record and its value stays unchanged.

The default output lists missing time points only. Use it to audit coverage without mixing new records with your observations. Choose Fill and retain detail rows when you want a combined table. A generated marker, a grid status and a normalized time are added as distinct columns. New rows have no source-file record because they were produced by your grid settings. Open their generation rule to inspect why they exist. Original rows keep their source references and raw timestamp text.

Import CSV, TSV or an explicitly selected XLSX data area, paste cells, or continue with an applied result from another workbench tool. Text imports ask you to confirm encoding, delimiter, header and parsing. Spreadsheet imports ask for the sheet, data range, header and hidden-content choices. Check stored values, displayed text and cached formula results before confirming. The tool does not recalculate spreadsheet formulas. A preview is only a sample; the complete import validates every record before processing.

Select the date or time field and its exact source format. An ambiguous day/month order must be resolved by the rule you select. Impossible dates such as February 30 are errors. Pure dates use calendar dates and support daily grids. Timestamps need explicit offsets or a declared source time zone. Select the IANA time zone used to display the grid and interpret unzoned range boundaries. The interface language and your computer location do not decide these settings for you.

Select grouping fields for independent stores, devices or another series identifier. Matching uses the complete selected text values without automatic trimming or case folding. With no grouping fields, the entire table is one series. Empty group keys require review unless you explicitly allow them as separate groups. Missing cells and empty strings remain distinct keys. A group found in the input can receive a specified range even when it has no points inside that range; the tool does not invent groups absent from your data.

The default range runs from the earliest valid time to the latest valid time in each group. A fixed range uses your start and end for every existing group. Both endpoints are inclusive when they lie on the chosen grid and pass the weekday and exclusion rules. An off-grid endpoint is a boundary, not a request to round a measurement. For a series from 00:00 to 00:30 with a 15-minute interval and midnight anchor, the expected points are 00:00, 00:15 and 00:30.

For timestamp grids, the local anchor sets the phase. With an anchor of 00:07 and that same range, the expected points become 00:07 and 00:22. The original 00:00 and 00:30 remain in the default combined output and receive off-grid statuses. Hourly, 15-minute and custom positive integer minute intervals use elapsed time from the anchor on the first local date of the range. A custom interval can cross midnight; it does not reset to a new phase every day.

Daily timestamp grids follow local calendar dates at the selected anchor. They do not add a fixed 24 hours to the preceding instant. Across a daylight saving change, two neighboring local midnights can be 23 or 25 elapsed hours apart. Intraday elapsed grids behave differently: during a fall-back transition, two local 01:00 times with different offsets are two separate instants. Both can appear in the generated list. An ambiguous or nonexistent unzoned source time is blocked. An ambiguous or nonexistent daily anchor is also blocked and requires a different anchor.

Choose included weekdays and optionally list excluded dates in YYYY-MM-DD form, one per line. These are explicit calendar filters in the grid display zone. They are not an official working-day calendar, and no automatic public-holiday database or make-up working-day schedule is supplied. Review the resulting first and last points rather than assuming that selecting Monday through Friday encodes every business rule used by your organization.

Estimate full time grid scans the confirmed input and shows the expected and missing counts before creating output rows. The group table shows each range and its first and last actual grid points. Inspect every relevant group, then confirm that grid. Run and review performs the complete generation using the same source and rules. Editing a rule, changing input or loading saved rules requires another estimate and confirmation. A source-content fingerprint also rejects an old confirmation when values change without a row-count change.

Expected time points equal present grid points plus missing points. These are distinct point counts. Duplicate points count instants with multiple source rows, while duplicate rows count all records participating in those duplicates. Off-grid, excluded-day and outside-range figures describe source rows. They should not be added indiscriminately to point counts. The reports provide individual source positions and raw times so you can inspect a count instead of trusting an unexplained total.

Two records with the same group and instant are a conflict even if their business values match. This release never aggregates them or chooses the first record. Invalid dates, unresolved empty grouping keys and duplicate points block generation and main-result export. Their reports remain available for diagnosis. Correct the source or deliberately resolve duplicates with another tool, then rerun the estimate. A gap checker cannot decide whether duplicate measurements represent a mistake or separate real observations.

By default, the combined result keeps every original record, including outside-range, excluded-day and off-grid observations. Their statuses explain why they do not satisfy the expected grid. Raw timestamp text is preserved. The normalized time column provides a consistent value for a later sort or calculation. Generated timestamps include the offset of the selected display zone; generated dates use YYYY-MM-DD. Groups follow first appearance in the input, and rows within each group are placed in chronological order.

New business cells are missing by default. A missing sales record does not prove zero sales, and a missing sensor observation does not prove a zero reading. You may add explicitly confirmed constant text to selected non-key fields of newly generated rows. Constants never overwrite original rows, including original blank values. The result reports how many new cells received constants. Interpolation, forward fill and backward fill are outside this task and are not performed automatically.

Grid-only retention removes original rows that fall outside the accepted grid, including excluded days. It requires a separate explicit confirmation because it changes the source-record population. A report retains the excluded originals. For a successful combined result, input rows plus generated rows minus excluded rows equals output rows. The missing-only result instead contains the generated count. Review these equations alongside the detailed reports before using the result in a later calculation.

Review the generated rows, issue statuses, excluded originals, group grid and settings in separate views. Searching a view does not limit a download. CSV and TSV exports are read back before download. ZIP output keeps reports as separate files; the original source table is not automatically included. Spreadsheet protection may prefix formula-like text, so review its affected count and choose the explicit raw-text option when another system needs the unchanged values.

Apply a verified combined result to continue with running totals or rolling calculations. A newly generated blank amount will trigger the next tool’s default missing-value policy. Explicitly ignoring it keeps its record position, whereas replacing it with zero gives that value a different meaning. Saved rules contain field names and any chosen constants; review them before sharing. Loading them remaps uniquely named columns but does not transfer approval of a new time grid.

All table processing takes place in a cancellable browser worker. Files stay in page memory and refreshing or closing clears the session. Limits include 10 MB per file, 100000 input or potential combined rows, 200 columns including metadata, 2000000 output cells and 200000 candidate points before calendar filtering. The expected grid itself is limited to 100000 points. Oversized ranges are rejected without truncation, and tasks have a 30-second timeout. Date-only values support years 0001 through 9999; time-zone conversion supports 1970 through 2100 and uses the browser’s time-zone data. Monthly intervals, automatic holidays and online synchronization are not included.