Before loading close dates, check week-and-weekday values, day-of-year values, and mixed date fields, then compare a small test import with known dates.
Key takeaways
- Check which date format each source value uses.
- Resolve missing weekdays before importing.
- Include year edge cases in your test batch.
- Compare imported dates with confirmed source dates.
A close date that is off by a few days can throw off a forecast, a commission report, or a pipeline review. When you load close dates into a CRM from a spreadsheet or database export, the risk often sits in values that look like dates but are read differently from how you meant them.
Week-based dates make this easy to get wrong. A value made of a year, a week number, and a weekday can be read correctly by one tool and wrongly by another. Because the wrong result still looks like a valid date, a quick glance at the column will not reveal it.
This post covers three checks to run before you load: the week-and-weekday format, the year-and-day-of-year format, and any value that mixes ISO week fields with ordinary calendar fields. It also covers year edge cases, which are easy to miss.
The parsing rules below come from the PostgreSQL documentation on data type formatting functions. Those rules describe how PostgreSQL's to_date and to_timestamp functions read a value. They do not describe your CRM or your spreadsheet. Use the examples to understand what can go wrong, then test your own data in the system you load into.
You do not need to be a database administrator to do this. You need a sample of your rows, a few dates you already know are correct, and a small test import. For related import checks, see sales data imports.
Week-and-weekday values need the weekday
In PostgreSQL, to_date reads a year, a week number, and a weekday with the pattern IYYY-IW-ID. The PostgreSQL documentation gives this example: to_date('2006-42-4', 'IYYY-IW-ID') returns the date 2006-10-19.
The weekday is the last part of that value, so it is the first place to look when a column is split, joined, or re-saved. Without it, the default applies: "If you omit the weekday it is assumed to be 1 (Monday)." (PostgreSQL documentation). If your rep meant Friday, the row loads on an earlier day than the Friday the rep had in mind.
To find these rows, count the parts in each value. A value in the year-week-weekday layout has two dashes, as in 2006-42-4. A year-and-week value without a weekday, such as 2006-42, is missing that part. Filter the column for those values and decide what each one should be before you load anything. If the source cannot tell you the weekday, go back to the rep or the deal record. Do not guess.
Then take a random sample of about ten rows. Work out the day each should land on, and compare that with the test import. If a row is off by a few days, check its weekday field first.
Year-and-day-of-year values need a day-number check
For year-and-day-of-year input, PostgreSQL's example shows that to_date('2006-291', 'IYYY-IDDD') also returns 2006-10-19, the same date as the week-based example above.
That pair makes a useful test. Put 2006-42-4 and 2006-291 in one test file and check that both load as the same date. If your system shows different dates, look at how it parses the values and how the import maps them to the close date field.
Day-of-year values are easy to damage without noticing. If the day field lost a digit or had two digits swapped during an export, the value still looks like a year followed by a number, but it points to a different day. Check the raw text of each value rather than how your spreadsheet displays it. Open the export in a plain text editor if you can, and compare the characters with the source system's record of the deal.
If the same date appears in both formats in your data, the two values should agree. When they do not, mark both rows and find out which one came from the wrong source before you decide which to keep.
PostgreSQL rejects mixed ISO week and calendar fields
The third check covers values that combine the two systems. The mixed-field restriction is explicit: "Attempting to enter a date using a mixture of ISO 8601 week-numbering fields and Gregorian date fields is nonsensical, and will cause an error."
The quoted rule is about PostgreSQL, but the check is worth running on any export. Look for a value that is partly week-based and partly calendar-based, such as a week number next to a month or a day of the month.
This often happens when a date is built from parts. A week number is copied from one column, a month and day come from another, and a formula or query joins them into one text value. Each part may be correct alone, but the joined result mixes two systems. Treat any such value as a bad row to fix.
To check, search the export for week numbers that appear next to a month, a day of the month, or a calendar year. Trace each match back to the formula, query, or manual edit that built it. Fix the build step rather than the single cell, because the same build can produce the same mixed value on the next export.
Year zero and long years need their own check
For year edge cases, the PostgreSQL documentation states: "An input of year zero is treated as 1 BC." It also says: "In to_timestamp and to_date, the YYYY conversion has a restriction when processing years with more than 4 digits."
These rules cover edge cases in the year field that an ordinary date check may not look for. A year of zero, or a year with more digits than you expect, should be flagged for review rather than loaded unchecked.
Scan the year field for a year of zero and for any year with five or more digits. Then check whether your export passes through to_date or to_timestamp at any step. If it does, run one long-year value and one year-zero value through that step before the full load, and see what comes out. The restriction applies to the YYYY conversion, so that step matters most.
Keep these edge cases in your test batch so they are checked on every load, not only the first.
Build a test file with known answers
Before you look at any import output, decide what each test row should show. Write the expected date next to each source value, using a calendar you trust. Doing this first keeps you from reading the output and then explaining it away.
The PostgreSQL examples already have expected results in the documentation. Add your own known dates too. Pick deals whose close dates you can confirm from a signed contract, an email from the rep, or a record someone has checked. Keep the expected dates in a separate column from the import results.
The table below shows how to record a checked deal.
| Source value | Pattern | Expected date | Where the expectation comes from |
|---|---|---|---|
| Example | Week and weekday text | Confirmed close date | Checked deal record |
Add a row for each edge case in your data. A value with the weekday missing is a good addition, because the expected result follows the Monday rule quoted earlier. Mark it clearly, so no one mistakes a rule-based result for a verified date.
Keep the file small enough to check by hand. Twenty to thirty rows covering every edge case are more useful than a thousand rows nobody reviews.
When the import finishes, compare each test row's result with its expected date, not with the number of rows the CRM reports as loaded. A matching count says the records arrived. It does not say the close dates are right.
Illustrative example: trace a changed date
Take 10 test rows with confirmed close dates. After importing, a row shows an earlier date than expected. Compare its original export text, its text after the spreadsheet step, and its CRM value. If the spreadsheet step changed the text, correct that step and rerun the 10 rows. Check every resulting date against its expected date before the full load.
Test the import in your CRM with a small batch
Before a full import, load a small batch and check each close date against its source. Include a week-and-weekday value, a day-of-year value, a value flagged for review, and a year-zero or long-year value if your data has one.
For HubSpot, use its property validation guide to locate the date property you want to edit. The guide also covers creating a property with a supported field type. Check these settings before the test, then confirm that the batch writes to the close date property and that the property accepts the values you send.
Other CRMs have their own import screens. Check your system's documentation for which property receives the close date, what format the import expects, and what the screen shows afterward. See also date and time formats for forecast exports.
Keep the original export while you test. If a test row looks wrong, compare three things: the original value, the value after any fix, and the value the CRM shows. Without the original, you cannot tell whether the error came from the source or from a step you added later.
Keep week-based values as text
Keep week-based values as text from export to import wherever your process allows. Each step that reformats a value is a chance to change it. If you can, set the column to text before you paste or import, and confirm the type after each step. Write down which steps touch the column, so you know where a value changed if it does.
When a value must pass through a spreadsheet, check it at the end of that step, not just at the start. Compare the final text with the exported text for a sample of rows and for every edge case row.
Read the whole export before you load it
Check all rows, not only the first few. A column can hold different kinds of values in different rows, and a problem far down the file will not show up in a quick preview. Sorting by the close date column helps, because it brings similar values together and makes an odd one easier to spot.
Look at the header row and any hidden columns. Hidden columns still exist in the file, so note which columns the import reads and which it ignores. Confirm that the close date column is the one you intended.
Decide in advance what a blank close date should mean, and check for cells that contain only spaces. Save a dated copy of the export and write down its row count before you start. If the file changes during testing, the copy and the count show what you actually tested.
Run these checks in order before each close date load
Run these checks in order, and do not start the full import until every item passes. If an item fails, fix the source and start the list again from the top.
- Week-and-weekday values. Review any missing parts.
- Day-of-year values. Compare the paired test values.
- Mixed fields. Trace flagged values back to the step that built them.
- Year edge cases. Review the results of the year-field tests.
- Test batch. Check the dates shown in your CRM.
- Original export. Compare fixes with the saved source.
Fix the source, then run the batch again
When a check fails, fix the value in the source file and rerun the test batch. Fixing only the CRM record leaves the source wrong, and the next export may carry the same error into your system.
Write down what you changed: the row, the original value, the corrected value, and who confirmed it. A short note in the load log is enough. If a date is questioned later, that note explains it. Use a close date change log to keep that explanation with the change.
Decide who owns the source file. The person who builds the export should fix formulas and queries, and the revenue lead should sign off on any change that moves a deal into a different period.
Once the test batch passes, run the full load, then check a sample against the source, paying extra attention to deals near a period boundary.
Why small date errors matter for close dates
A close date that moves by a few days can change the period a deal appears in. If a deal that should close in one month lands in the next, each month's forecast changes. A small shift at a quarter boundary can move revenue into a different quarter.
This is why these checks focus on values that are valid but wrong. An invalid value can fail to load, which makes the problem visible. A valid value on the wrong day can load silently, and the mistake may surface later in a report that someone relies on.
Start with a test file today
Today, save an unchanged export and select ten rows with confirmed close dates. Write down their expected dates before running a small test import.
Questions before the load
Does a successful import prove the dates are correct?
No; compare the imported close dates with their confirmed source dates. Review differences before starting the full load.
Where should I investigate a date mismatch?
Compare the original value, the value after any fix, and the CRM value. Use the first difference to identify the step to investigate.