Skip down to the research record

Close date mismatches: 5 mistakes in forecast records

Quota Forecast Editorial Team · Published · 10 min read

When close dates disagree across forecast reports, investigate five interpretation mistakes: short years, weekday numbering, UTC handling, modifier order, and shared formatting assumptions. Metabase documents that personal settings can override global date defaults, so a different display does not prove the underlying record changed.

Key takeaways

  • Check short-year pivots before assuming errors. PostgreSQL interprets two-digit years by adding 2000 if the value is less than 70, otherwise adding 1900.
  • Identify which weekday method produced the number. Python’s weekday() returns 2 for a Wednesday, while isoweekday() returns 3 for the same day, according to the Python documentation.
  • Verify time zone handling in calculations. SQLite date and time functions use UTC or “zulu” time, as stated in its Date And Time Functions guide.
  • Respect modifier evaluation order. In SQLite, modifiers are applied from left to right, making sequence critical for accurate results according to the SQLite documentation.
  • Separate display settings from stored data. Differences in how dates appear often stem from interpretation rules rather than changes to the underlying record.

Close date mismatches: the interpretation mistakes to investigate

Five specific interpretation errors often masquerade as data conflicts in forecast reports. The first assumes that two-digit year inputs resolve identically across all systems. The second confuses different weekday numbering conventions used by various calculation libraries. The third overlooks how time-zone boundaries affect which calendar day a timestamp belongs to. The fourth ignores the sequential order in which date modifiers apply during calculations. The fifth assumes that everyone viewing a report sees the same date format because they use the same instance.

These are parsing and display issues, not necessarily evidence of deal slippage. A date appearing differently in one tool compared with another may reflect how that tool interprets numeric strings or applies localization rules. For example, a system might interpret a six-digit input as a concatenated date rather than a simple month-day sequence. Another might assign a Wednesday the number two under one convention and three under another. These differences change the calculated value without altering the original stored fact.

Before attributing a discrepancy to a sales rep changing a commit date, check the input string itself. If you need background on forecast categories, start there. Verify whether the source data contains leading zeros or abbreviated years. Confirm which weekday algorithm generated any derived fields. Ensure your team understands whether the reporting engine processes timestamps in UTC before converting them for local display. Finally, review individual user preferences, since global settings do not lock every person’s view.

Input mistakes: short years and numeric date strings

When a forecast record shows a close date that looks wrong, the first place to look is how the system parsed the original input. PostgreSQL documentation explains how numeric strings are interpreted before any business logic runs. If the input contains only numbers, it is treated as either a single field or an ISO 8601 concatenated date, such as 19990113 for January 13, 1999 PostgreSQL. This rule applies to the PostgreSQL parser specifically. Other tools may handle numeric strings differently, so compare how forecast tools parse dates.

A common source of confusion involves six- or eight-digit inputs. The documentation states that if there are eight or six digits and no other date fields have been previously read, the system interprets them as a “concatenated date,” like 19990118 or 990118 PostgreSQL. A two-digit year in this context triggers a specific century pivot. If the year field is less than 70, the system adds 2000; otherwise, it adds 1900 PostgreSQL. Inspect the original input string to determine which century was applied under these rules. Check whether your data entry uses this convention or a different one.

Leading zeros also affect interpretation for early years. Gregorian years AD 1–99 can be entered using four digits with leading zeros, so 0099 represents AD 99 PostgreSQL. While rare in modern sales forecasts, this rule matters if historical data or legacy imports contain such values. Additionally, separators change how the system classifies the string. If a numeric string includes a dash, slash, or two or more dots, it is identified as a date string that might include a text month PostgreSQL. Distinguishing between a pure numeric concatenation and a separated date string prevents misreading the intended close date.

Calculation mistakes: weekday numbers, UTC and modifier order

Check the calculation logic before assuming a date value changed. Three documented behaviors in Python and SQLite create mismatches when different scripts or queries process the same record.

Weekday numbering conventions Python’s datetime module provides two methods that return different numeric values for the same day of the week. According to Python, the weekday() method returns 2 for a Wednesday, as shown by the example date(2002, 12, 4).weekday() == 2. The same source documents that isoweekday() returns 3 for that identical Wednesday, illustrated by date(2002, 12, 4).isoweekday() == 3. If your forecast pipeline uses one method for filtering and another for display, the numeric output will differ even though the underlying calendar date remains static. Verify which method each script calls.

UTC handling in date functions SQLite’s date and time functions operate on a specific time standard. The Date And Time Functions documentation states that these functions use UTC or “zulu” time. If a close date is stored with a local time zone offset but processed through a query expecting UTC, the resulting date string may shift by a day depending on the hour. Confirm whether your data ingestion converts time zones before storage or if the query layer handles the conversion. A mismatch here changes the calculated date without any manual edit to the record.

Modifier application order When adjusting dates using modifiers, the sequence matters. The SQLite documentation notes that modifiers are applied from left to right; order is important. Applying the same modifiers in a different sequence can therefore change the result. Review the exact order of modifiers in any automated date-shift logic to ensure it matches the intended business rule.

Display mistakes: instance defaults and personal overrides

A mismatch in how a close date appears on screen can stem from formatting choices rather than a change to the underlying record. Metabase documentation notes that localization settings allow you to set global language and formatting defaults for dates, times, numbers, and currencies Metabase. However, these instance-level defaults do not dictate every person’s view. People can override these settings in their personal account settings. This means two users looking at the same forecast report may see different date representations if one has adjusted their personal preferences while the other relies on the instance default.

When investigating a disputed close date, check whether the discrepancy arises from display logic. Compare the applicable formatting settings between the user reporting the issue and the system administrator. If one user sees a date in a format like MM/DD/YYYY and another sees DD/MM/YYYY, the difference is likely due to personal overrides rather than a data entry error. Verify which setting is active for each viewer before concluding that the deal’s close date has moved. A different-looking date may simply reflect a different interpretation of the same stored value under local configuration.

Date interpretation reference table by publisher

The table summarizes each publisher’s documented date behavior.

PublisherDocumented Behavior
PostgreSQLInterprets eight- or six-digit numeric strings as concatenated dates if no other date fields were previously read, such as 19990118 or 990118 PostgreSQL.
PostgreSQLApplies a pivot where a two-digit year field less than 70 adds 2000, while values of 70 or higher add 1900 PostgreSQL.
PythonRepresents a Wednesday as weekday number 2 using the weekday() method in its documented examples Python.
SQLiteUses UTC or “zulu” time for its date and time functions SQLite.
MetabaseAllows people to override global localization settings in their personal account settings Metabase.

Illustrative example: one date, two displays

Imagine one stored close date. A forecast script calls weekday() and gets 2 for a Wednesday, while a dashboard calls isoweekday() and gets 3 for the same day. The two reports disagree, but the record never changed. A separate input uses a two-digit year of 25, which falls below 70, so the pivot adds 2000 rather than 1900. Before you label this as slippage, check which method and year rule each tool used.

Keep the original date and its interpretation together

Take one disputed close date and compare its original input, applicable date convention and displayed format before classifying it as deal movement. Start by locating the raw value entered into the system. If the input is a short numeric string, verify how the backend parses it, since specific database engines apply documented rules for concatenated dates and year pivots that can shift the resulting calendar day without any user editing the record. Next, check the calculation layer for weekday numbering or time-zone handling that might alter the derived date in reports. Finally, inspect the display settings to confirm whether a personal override changed the visual representation while leaving the stored value intact.

When investigating a mismatch, determine if such automation ran between the initial entry and the current view. If the platform applies an automated change, the “movement” is a rule-based adjustment rather than a manual slip. Document the specific automation rule active at the time of the discrepancy. This allows you to distinguish between a system-driven update and a human error during data entry. If the record itself changed, read the close date change log.

Next step: today, pull the raw value of one disputed close date and note its input format, weekday method and display setting before you change anything in the record.

FAQ: investigating disputed close dates

What date interpretation mistakes can explain close date mismatches?

Short year assumptions, weekday numbering conventions, UTC handling, modifier order, and shared formatting settings are the primary areas to investigate. These factors often create visual discrepancies without indicating that a deal actually moved. Check each convention before attributing a difference to record edits.

Does the weekday numbering difference apply if our forecast process uses Python?

Yes, Python documents both weekday and isoweekday methods, and they return different values for the same day. Verify which method your scripts use when comparing numeric outputs.

How does PostgreSQL interpret a two-digit year below seventy?

PostgreSQL adds 2000 to a two-digit year below 70 and adds 1900 to any other two-digit year. This documented procedure determines how two-digit years map to four-digit years in date parsing. Inspect the original input string to see if this pivot was applied.

Can personal Metabase settings override instance date formatting?

Yes, people can override these settings in their personal account settings within Metabase. Instance-level defaults do not establish every person’s display because individual preferences take precedence. Compare the applicable formatting settings when investigating a disputed representation to identify where the difference originates.

Which order does SQLite use when applying date modifiers?

SQLite applies modifiers from left to right, so changing their sequence can change the resulting date or time value. Always verify the modifier sequence in any calculation that shifts dates relative to a baseline.

Sources