Checking Date Ranges and Gaps in Time Series Data
You have a year of transaction data. Or so you were told. But does the file actually cover January through December? Are there missing weeks? Did something go wrong in March where the record count drops to zero?
These aren't hypothetical problems. Missing date ranges are one of the most common and most damaging data issues because they're invisible. Your totals look fine. Your averages look reasonable. But you're making decisions based on 10 months of data thinking it's 12.
See the time series at a glance
Click any date column in ExploreMyData to open the Date Column Explorer. It shows a time-series chart: a count of records per time period across the full range of your data.
The default view uses month granularity, which is the right starting point for most datasets. You'll immediately see the shape of your data over time. A healthy dataset shows a relatively consistent pattern. Gaps show up as dips to zero. Partial months at the start or end show up as shorter bars.
| Month | Records | Bar |
|---|---|---|
| Jan 2025 | 4,201 | |
| Feb 2025 | 4,089 | |
| Mar 2025 | 0 | |
| Apr 2025 | 3,977 | |
| May 2025 | 4,310 | |
| Jun 2025 | 4,188 |
Monthly record counts for order_date. March 2025 has zero records - a complete data gap that would be invisible in a year-to-date total.
Check the boundaries first
Before worrying about gaps in the middle, confirm the start and end dates match your expectations. The chart's x-axis shows you the full range. If someone told you this is "2025 data" but the chart starts in March 2025 and ends in November 2025, you're missing four months.
This is surprisingly common. Data exports often have implicit date filters that nobody mentions. Or the source system didn't go live until March, so those first two months simply don't exist. Either way, you need to know this before doing year-over-year comparisons or calculating annual totals.
Change granularity to find different patterns
The Date Column Explorer lets you switch between day, week, month, and year granularity. Each level reveals different problems.
Monthly shows you the big picture. Missing months are obvious. Seasonal trends show up clearly. Use this first.
Weekly shows medium-scale gaps. A system outage that lasted a week might not show up at monthly granularity (the month still has data), but it's clearly visible at weekly level.
Daily reveals the fine detail. Weekday vs. weekend patterns in retail data. Holidays where nothing was recorded. The specific days where a gap starts and ends.
Yearly is useful for multi-year datasets. If you have five years of data, yearly granularity shows you whether each year has roughly the same volume or if there's a trend.
| Week starting | Records | Bar |
|---|---|---|
| Feb 17, 2025 | 1,042 | |
| Feb 24, 2025 | 988 | |
| Mar 3, 2025 | 0 | |
| Mar 10, 2025 | 0 | |
| Mar 17, 2025 | 154 | |
| Mar 24, 2025 | 1,031 |
Weekly granularity reveals two consecutive empty weeks (Mar 3–16) and a partial week (Mar 17) as the system came back online. The monthly view showed only one blank bar.
Extract date parts to quantify gaps
The chart shows you gaps visually. To quantify them, use Extract Date to pull month and year into their own columns. Then you can group by month/year and count records per period.
For example, extract the month from an "order_date" column to create an "order_month" column. Now you can see the exact counts: January has 4,201 orders, February has 4,089, March has 0, April has 3,977. That zero in March is now a concrete number, not just a dip in a chart.
This approach also lets you compare periods. If February has 4,089 records and April has 3,977, you can estimate that March should have had roughly 4,000. That's useful when you need to communicate the impact of missing data to someone else.
Use filters to investigate suspicious periods
When you spot a gap or an unusual spike, filter the data to just that time period. Use the Filter operation with a date range to isolate the rows around the anomaly.
Say the chart shows a spike in December that's 3x the normal volume. Filter to December and look at the data. Are there duplicate records? Was there a bulk import? Did a different system's data get merged in? The rows around the anomaly usually contain the explanation.
For gaps, filter to the week just before and after the missing period. Look at the last records before the gap and the first records after. Sometimes you'll see a change in format, source, or ID sequence that explains what happened.
Measure gaps between consecutive records
For data that should arrive at regular intervals (daily reports, hourly sensor readings), use Date Diff to calculate the gap between each row and the previous one. Sort the data by date first, then add a Date Diff column.
In a daily dataset, most gaps should be 1 day. Sort the Date Diff column descending. The largest gaps appear at the top, showing you exactly where the breaks are and how long they lasted. A 3-day gap might be a weekend. A 14-day gap is a problem.
| order_id | order_date | days_since_prev | Notes |
|---|---|---|---|
| 10834 | 2025-03-17 | 14 | Gap - investigate |
| 10122 | 2025-01-06 | 3 | Possible weekend |
| 10441 | 2025-02-04 | 1 | Normal |
| 10442 | 2025-02-05 | 1 | Normal |
| 10443 | 2025-02-06 | 1 | Normal |
Sorted by days_since_prev descending. The 14-day gap at the top immediately surfaces the outage period.
Common date range problems
After checking enough datasets, certain patterns keep appearing:
- Partial first/last month: Data starts on the 15th or ends on the 3rd. Whoever pulled the export used the wrong date range.
- Holiday gaps: No records during the last week of December. Either the business was closed or the recording system was paused.
- System migration gaps: A gap of a few days or weeks followed by a change in data patterns (new columns, different ID formats). The old system was turned off before the new one was fully running.
- Timezone issues: Records that appear to be missing on the 1st of each month because timestamps were stored in UTC but the export filtered by local time.
- Future dates: Records dated next year or in 2099. Usually placeholder dates for "no end date" or data entry errors.
Check your dates before you analyze
The date check takes two minutes. Open the Date Column Explorer, confirm the range, scan for gaps, switch to weekly granularity to catch smaller holes. That's it.
Skipping this step means you might calculate a "monthly average" that divides by 12 when you only have 10 months of data. Or build a year-over-year comparison where one year is missing Q3. The numbers will look plausible, which is exactly what makes date gaps dangerous.