Skip down to the research record

3 time zone checks before you trust a deal close date

Quota Forecast Editorial Team · Published · 12 min read

Three checks keep a deal close date on the same calendar day in your CRM, database and forecast, across time zones and daylight saving rules. A rep picks a day, the system saves it, and the forecast reads it back later. The trouble starts when the saved value and the forecast value land on different days. A deal that was counted in one quarter shows up in the next one, and the two reports disagree even though both came from the same field.

Time zones are a common cause of that gap. A date picked in one office can be stored as a moment that falls on a different calendar day when another system reads it. Daylight saving rules add a second layer, because they change on dates most people never track. Databases, CRMs, and export tools each handle these details differently, so a value that looks right on screen can change once it is saved, moved, or exported.

This post covers three checks to run before you trust a close date. Each one answers a different question: which zone the date belongs to, what the system actually stored, and whether the zone setting will keep working when the rules change. We carry one example through the post: a renewal the rep expects to close on the last day of the quarter, with the forecast counting it in that quarter.

The sources cited here are official pages from PostgreSQL, Pipedrive, and Microsoft. Treat each check as a test to run on your own data rather than a guarantee, because your database, CRM settings and forecast tool may behave differently.

The example deal

Our renewal lives in a sales team's CRM. The rep works in one office. The finance team reads the forecast from a database running on a server set to a different zone. The rep enters a close date of the last day of the quarter, and the forecast report adds up deals by close date within that quarter.

On the rep's screen, the close date reads as the last day of the quarter. That is what the rep meant, and it is what they see every time they open the record. In the database, though, the field holds a single moment in time. Depending on how the value was converted on the way in and out, that moment can read as the last day of the quarter in one zone and the first day of the next quarter in another.

Nothing about the entry is wrong. The problem is a mismatch between what the rep thinks the field holds, a calendar day, and what the system stores, a point in time. The three checks find where that mismatch happens and fix it before a forecast depends on it.

The Microsoft documentation for Dataverse columns gives a concrete example of a stored time value. It says the time value is "stored as 12:00 AM (00:00:00) in the system." (Create and edit columns in Dataverse using Power Apps) Whether your own CRM stores a midnight value the same way is a question for your settings. The example still shows why a close date needs a clear meaning before anyone builds a report on it. A midnight value is a specific moment, and a specific moment can fall on different days in different zones.

Our example has three facts that matter for the checks. The meaning is "last day of the quarter in the forecast zone." The stored value is whatever the system kept after the save. The zone is the one the forecast uses. Keep these in mind as we go.

First check: decide which zone owns the close date

Before you touch a report or query, write down two things: the zone the forecast uses, and whether the close date is a calendar day or a moment. The answers should match. If the forecast counts whole days in the company's home zone, the close date should mean a whole day in that zone. If your system stores a moment, convert it to that zone before reading the day. Either approach can work. The mistake is leaving the question open so each tool answers it on its own.

Many teams skip this step because the field is named "close date" and everyone assumes it means the same thing. A name does not carry a zone. Two tools can read the same stored value and produce two different days, and neither is wrong by its own logic. Writing the zone down gives the whole team one definition to test against.

Who decides matters as much as what is decided. The forecast owner needs the answer, while the sales operations owner controls the import and the field settings. Put both names next to the decision in your documentation. If the forecast owner says "last day of the quarter in the home zone" and the operations owner has never heard that phrase, the decision is not agreed yet, and the field will drift the next time someone edits it.

Formats matter too. The PostgreSQL documentation on date and time types says an offset given to seconds is not allowed in ISO 8601. It also says that an offset otherwise appears as a signed numeric offset in ISO 8601 basic format, written as hh or hhmm. (PostgreSQL: Date/Time Types) When you move close dates between systems, check the offset format in each tool. A value one tool writes one way may be read differently by another, and the difference may only show up on the day the forecast runs. For more on formats, see Date and time formats: 4 tools for forecast exports.

The same page notes that ISO 8601 calls for an uppercase letter T to separate the date from the time. (PostgreSQL: Date/Time Types) That detail is small, but it breaks when a close date passes through a spreadsheet, a script, and a CRM export in sequence. Pick one format for close dates, write it in your data dictionary, and apply it in every import and export. If a value arrives in another format, reject it or convert it on purpose rather than letting a parser guess.

For the example deal, the forecast counts whole quarters in the home zone, so the close date should mean "last day of the quarter in the home zone." Put that meaning in the field description, the data dictionary, or the import setup. A short label such as "close date (home zone)" stops the next person who edits the record from guessing.

Test the decision with a boundary case. Pick a deal whose close date sits at the very end of the quarter, since a small shift in time can move it across a boundary. Ask the team what the forecast should show for that deal. If the answer depends on who you ask, the decision is not finished.

Second check: read the stored value, not the screen

A screen is a view. It shows the value after the tool has converted it for the person looking at it. The stored value can be different, and a forecast built on a query, an export, or an integration reads the stored value or a conversion of it. So the question is simple: does the value the system holds point to the same day the forecast expects?

An export shows the value after the export step converts it, so compare it with a raw query result. The Pipedrive help article on exporting data says you can export customized data from the list view for deals, leads, projects, contacts and organizations. (Exporting data from Pipedrive) An export gives you a file of the customized data you selected, so you can compare the close date column with the forecast without relying on the view the rep sees. Check the export settings in your own CRM as well, because the columns and date formats may differ.

For the example deal, export the renewal and find its close date in the file. Confirm that it reads as the last day of the quarter in the forecast zone. If it reads as the first day of the next quarter, the problem sits in storage or in the conversion between the database and the file. It is not what the rep typed, and the rep cannot fix it by editing the record again.

If you run your own database, you can build the same check in a query. The PostgreSQL documentation notes that the formatting function to_char is a more flexible way to format date and time output. (PostgreSQL: Date/Time Types) Print the close date in the forecast zone next to the raw stored value. If the two columns disagree about the day, you have found the gap before the forecast did.

When the export and the screen disagree, do not start by changing the record. List the three places a conversion can happen: when the value is written, when the export reads it, and when it is displayed to the user. Check each one with the same deal. Often the first conversion is correct and a later one applies a zone the team did not expect. Once you know which step changes the day, the fix is usually a setting in that step rather than a change to the data.

Keep the check small. Choose deals that close near a quarter boundary, since they show the gap first, and recheck them after each change to the import, the export, or the zone settings. A short, repeated check catches shifts that a single manual test would miss.

Record the result: the export settings, the query, the zone name you used, and the date you ran the check. The next person who asks why a deal moved between quarters will need that record, and without it the team tends to argue from memory. For a related record of close date moves, see Close date change log: why the date moved.

Third check: use named zones, and expect the rules to change

A fixed offset is a number, a set difference from a reference time that stays the same all year. A named zone is a label for a place, and it follows the rules that place uses, including changes to daylight saving time. The difference matters because a fixed offset typed in years ago may be wrong once the rules change.

The PostgreSQL documentation says that time zones are "prone to arbitrary changes, particularly with respect to daylight-savings rules." (PostgreSQL: Date/Time Types) For a forecast that depends on a day boundary, that is the reason to store a named zone. A close date near the end of a quarter is exactly where a small change in the rules can move a deal into a different quarter.

A fixed offset looks safe because it is simple. It is easy to read in a config file, and it never seems to move. But the number was chosen for one set of rules, and the forecast keeps running after those rules change. A team that stored a fixed offset will not see the problem until a deal near the boundary lands in the wrong quarter, and by then the report has already gone out. Storing the zone name costs a little more setup and spares the team that surprise.

Named zones also need to be names your database recognizes. The PostgreSQL documentation says the recognized time zone names are listed in the pg_timezone_names view. (PostgreSQL: Date/Time Types) Query that view and confirm that every zone name your system stores appears in it. Build that check into the import so an unknown name fails loudly rather than passing through quietly.

For the example deal, the forecast zone should be stored as a named zone, the one the company uses for its books, and not as a number typed in by hand. Test the zone name against the view, then rerun the export and query checks on a deal near the quarter boundary.

It is also worth reviewing defaults. Some systems may fill in a default zone when a value has none, and that default may not match the forecast zone. Check the defaults in your CRM, your import tool, and your database, and record them beside the forecast zone so the two can be compared at a glance.

Running the three checks on the example deal

Here is the example run from start to finish. Each step uses the output of the one before it.

  1. Set the meaning. The renewal's close date means the last day of the quarter in the forecast zone. Write that on the field and in the import setup.
  2. Read back. Export the renewal and confirm that its close date reads as the last day of the quarter. Then run a query that prints the stored value beside the forecast-zone value. Both should agree on the day.
  3. Name the zone. If the forecast database is PostgreSQL, confirm that the zone name stored for the forecast appears in the pg_timezone_names view. Then repeat step 2 for a deal near the quarter boundary after any daylight saving change.

If all three steps agree, the renewal counts in the right quarter, and the forecast and the database tell the same story. If one step disagrees, fix that step before the others, because the checks depend on each other. A correct zone name does not help if the field has no agreed meaning, and a clear meaning does not help if the stored value is converted in a way nobody can see.

The order matters for a practical reason. Step 1 decides what the number should mean. Step 2 shows what the system holds. Step 3 shows whether the meaning will survive a future change. Doing them out of order makes it hard to tell which problem you are fixing, and a team that jumps to step 3 first often tunes a zone setting that was never the cause.

To explain this to a team, show the same deal in three views: the rep's screen, the export, and the forecast report. If the screen and the export both say the last day of the quarter, but the report puts the deal in the next quarter, the problem is in the report's zone setting. If all three disagree, start at step 1 and work down. Side-by-side views turn a debate about who is right into a question about which conversion changed the day.

Where to start this week

Pick the close date field that feeds your largest forecast. Run the three checks on the deals that close near a quarter boundary, because they show the gap first. For a related set of rules, see Compare close dates: 3 rules for date and time fields. Write down the decision on the field meaning, the zone name, and the export or query you used to confirm the stored value. A single page with those three things is enough to start.

Then bring the result to the people who own the import, the CRM settings, and the forecast report. Ask each owner the same question: what does this field mean, and which zone does it use? Two owners who give different answers are the signal that the work is not finished, and that is worth knowing before the next close.

Finally, put the checks on a calendar. Run them again when someone changes an import, a CRM setting, or a database connection, and when daylight saving rules change in a zone your team uses. A forecast that is off by one day can cost a team trust in its numbers, and a short check now is cheaper than a long reconciliation later.

Sources