Skip down to the research record

Close-date imports: 6 checks for date errors

Quota Forecast Editorial Team · Published · 12 min read

Close-date imports can fail or store wrong dates when tools rewrite years, accept swapped dates or encounter skipped or repeated local times.

Key takeaways

  • Confirm intended years before correcting short or missing years.
  • Check swapped-looking dates with the deal owner, even when the month is valid.
  • Test date variations before loading the full file.
  • Compare stored CRM dates with the intended values.

People who load close dates tend to follow the same routine. Someone exports deals from the CRM, edits the close date column in a spreadsheet, and uploads the file again. The import reports success, the row count matches, and nobody reads the dates until a forecast looks wrong.

Most of the damage comes from two kinds of date error. The first is a year the tool reads differently from the way the person meant it. The second is a date the calendar does not allow, or a time the local clock skips or repeats. Both pass a quick look at the file. This post follows one renewal deal through both errors, then ends with checks you can run before the file loads.

The cost is rarely the import itself. It is the next decision built on a wrong date: a pipeline review that drops a deal, a hiring plan that assumes a quarter that never closes, or a rep who chases a deal that is already stale in the system. None of those show up as data errors, which is why they last.

The deal we will follow

The example is a single renewal deal with a close date of 2015-03-29. That is the date the forecast should show, and every example below comes back to it.

The same close date reaches the import in four shapes, because each person and system handles dates a little differently:

  • In an older planning sheet, the rep typed the year as two digits next to the month and day.
  • In another sheet, the rep left the year off and typed only the month and day.
  • In a third sheet, a manual edit swapped the day and the month, so the day sits where the month belongs.
  • In the CRM export, the date arrives with a clock time attached.

The first two shapes are year problems. The last two are dates that can break even when the year is right.

Why a quick look misses both errors

A quick look checks the shape of the file. Are there the right number of rows? Do the columns line up? Does each cell hold something that looks like a date? All four shapes pass that test. A two-digit year looks like a date. A month and day with no year looks like a date. A swapped entry has the same parts in a different order. A date with a clock time is still a date.

The import report adds to the false comfort. It counts how many rows loaded and how many failed, and it counts a row that loaded with a wrong year as a success. Nothing in that report says the close date is wrong. The error becomes visible only when someone compares the stored value with what the rep meant.

Forecast totals rarely catch it either. A deal with a close date in 1900 does not land in the current quarter, so the total simply comes out lower. A lower total looks like a slow quarter, not a bad import.

Error one: a year the tool rewrites

A spreadsheet often holds years as two digits, so the import tool has to decide what those digits mean. The PostgreSQL documentation on date input describes one such rule: "If BC was not specified, and if the year field was two digits in length, then adjust the year to four digits" (PostgreSQL documentation).

That rule belongs to one database, not to every import tool. PostgreSQL expands the two-digit year to four digits. Your spreadsheet, CRM and import tool may each choose a different century, and the deal list will show whichever one the import used. So check the stored value in the system, not the value in the source file.

A useful test is to load two rows: the renewal date written in full, and the same date with a two-digit year. Compare what each one stores. The difference tells you what your tool does to two-digit years before a real file depends on it.

The effect on reports is larger than it first appears. A close date in the wrong century drops the deal out of any "closing this quarter" filter, and it can make an on-track deal look overdue. Neither outcome produces an error message.

The month-and-day entry with no year is a harder case. The Python datetime documentation describes what a parser fills in when fields are missing: "1900 is substituted for the year, and 1 for the month and day" (Python documentation). A parser that fills gaps this way can turn a bare month and day into a close date in 1900. The date is valid in form, so no validation message appears. The deal just looks closed more than a century ago.

But your import may run an older version, or may not use that library at all. Do not count on the error appearing. Look at what was stored.

The lesson of the first error is that a parsed date and a typed date can differ with no warning. The simplest fix is to remove the decision: write the year in full. Put the year shapes side by side. For the renewal deal, the short-year row, the no-year row and the full-year row should store the same value in the CRM after import. If they do not, write down what each one stored, and keep that note with the import settings.

Error two: a date that cannot happen

The second error covers dates the calendar does not allow, and times the local clock skips or repeats. The out-of-range case is the easier one, because a careful parser refuses it. The PostgreSQL rules page says the parser will "Throw an error if a month or day field is found to be out of range." A swapped entry puts a day value where a month value belongs. This rule rejects an out-of-range month; a swap that leaves a valid month can pass the range check.

A loud failure is the one to want. It gives you a row number and a reason, so the person who knows the intended date can fix it. A silent failure gives you neither, and it is the kind that reaches the forecast. When you evaluate a tool, ask which kind of failure you will get for each of the four shapes.

Not every tool is that strict. A loader that stores text as typed will accept a swapped date and pass it along. The error then shows up later, as a deal with a close date no calendar contains. The people who see it may blame the forecast rather than the file, and the fix becomes a hunt through rows that all look fine.

The clock case is harder. The renewal's export stamps its close date with a clock time, and that time falls in a gap. The pandas timeseries guide shows what a strict parser says in that situation: "ValueError: 2015-03-29 02:30:00 is a nonexistent time due to daylight savings time" (pandas documentation). In a time zone whose clocks jump forward at that point, 02:30 on that day never appears on the local clock. Depending on the tool, the row fails, or it is stored at some other time than the export showed.

The opposite case causes similar trouble. The same guide warns: "This will fail as there are ambiguous times ('11/06/2011 01:00')." When clocks fall back, one hour repeats, so a local time such as 01:00 occurs twice in that zone. The tool cannot tell which occurrence the rep meant.

Python's datetime carries a fold value, and its documentation notes: "Changed in version 3.6: The fold value is copied to the returned time object."

For a close date, a plain day is usually what the forecast needs. A date has no clock time and no time zone, so it avoids both problems. If the system insists on a time, store the time zone with it, and decide which zone the forecast reads from. Do not let the import pick the zone for you. See the related time zone checks when reviewing that choice.

Checking the file before it loads

Run these checks before anyone uploads the file. Each one helps catch a row that might otherwise load looking correct.

  • Two-digit year: expand it to four digits, using the year the rep confirms.
  • No year: confirm the year with the deal owner before loading.
  • Swapped day and month: confirm swapped-looking rows with the deal owner; range checks miss swaps that leave valid months.
  • Clock time: remove it if only a date is needed, or store a time zone with it.

Illustrative example: test rows before a full upload

In an illustrative test of 10 rows, 2 store a different year from the one intended and 8 match. The import's row count alone would hide the problem. Hold the full upload, confirm the intended years and repeat the test after correcting the affected rows.

Then run the full list:

  1. Pick one date format and write it down for the whole team. Use that form in every sheet that feeds the import.
  2. Sort the close date column and read the earliest values first. A date in 1900 is a red flag, and so is any year far from the rest of the column. The renewal deal should read 2015-03-29 wherever it appears.
  3. Look for two-digit years. Convert each short year to four digits using someone who knows the intended year, rather than leaving the tool to expand it.
  4. Check month and day ranges. A month beyond the last month of the year is wrong, and so is a day beyond the end of its month. A swapped row can still have a valid month.
  5. Strip clock times from close dates unless the forecast needs them. A time adds nothing to a date used only to group deals by quarter.
  6. Test with a handful of rows before the full file. Include the renewal row and at least one row you already know is right. Then check the imported values in the CRM, not the sheet. The sheet shows what was typed, and the CRM shows what the tool stored.

Split the checks between owners. Reps should take the first three, since they know which year and day they meant. The operations owner should take the last three, since they see the export format and the import settings. A check with no owner tends to get skipped in a busy week, and a skipped check is often the one that would have caught the error.

Fixing rows without guessing

When a row fails a check, the pull is to fix it fast and move on. Resist that. A guessed year is still a guess, even when it looks tidy. Confirm a short-year date against the deal record or with the rep who owns the deal, because only they know which year was meant.

Decide ahead of time who may approve a corrected close date. In most teams that is the rep plus one reviewer in operations, and the approval goes in the log. Without that rule, a hurried correction can be undone by the next import, which loads the old value again from the export.

Keep a short log as you go: the deal name, the original cell, the corrected value, and who confirmed it. The log lets you explain the import later, when a forecast number needs defending. It also shows which reps keep producing the same shape of error. That is a coaching conversation, not a data problem. Use a close date change log to keep the reason alongside the correction.

Be careful with bulk fixes. A find-and-replace on a whole column can change a correct value in a row you did not check. Fix verified rows in batches, and run the checks again after each batch. Rows you cannot confirm should wait for their own import rather than go in with a guessed date. Review the bulk-edit checks before changing a whole column.

What to tell the reps

Reps do not need a lesson on calendars. They need two habits they can follow in a spreadsheet. Write the year in full, every time. Use the agreed format, and do not paste dates from a chat message or an email, because those often arrive with a different order or a clock time attached.

Ask reps to type dates into cells formatted as dates, not as general text, if the sheet supports it. A text-formatted cell holds a short date exactly as typed, and that is the value the import then has to guess about.

Setting up the next import

Most of this comes back every quarter, so the fix is a process rather than a single cleanup. Choose one date format for the whole export, and make the spreadsheet enforce it. Set the close date column to a date type where the tool allows it, and lock those format cells so a paste does not change them.

Write down the import settings and who can change them. A setting that changes how two-digit years are read, or whether a time is kept, can alter every close date in the next file without anyone editing a row. Store the settings next to the sheet.

Ask the import to reject rows that fail its date checks, rather than coerce them into something close. A rejected row is a visible problem with a row number. A coerced row is a quiet wrong date in the forecast. If the tool offers a preview, read its close date column first, because that is where the year and the clock time show up.

After each import, run two checks in the CRM. Count deals whose close date reads 1900, and list open deals whose close dates are far in the past. Both lists should be short or empty. If they are not, they point to the rows to examine first.

At the end of each quarter, compare the close dates that moved into the next quarter with the reps' own list of expected closes. A bad year can sit in the CRM for weeks, and a quarter-end review is where someone still remembers what the deal was supposed to be.

Before the next upload

A clean import once is not proof of a clean import next quarter.

Today, prepare those test rows and compare their imported close dates with the intended dates before uploading the full file.

What to check before importing

Why can an import report success with a wrong date?

A row can load successfully while storing a different year from the one intended. Row counts measure loading, so inspect the stored close dates too.

What should happen to a date nobody can confirm?

Hold that row out of the import until the intended date is confirmed. Keep its original value and the reason it needs review in the correction log.

Related guides: Close date mismatches: 5 mistakes in forecast records · Commit evidence: 5 checks before promising a close date.

Sources