Understanding Dates and Time Zones

Understanding Dates and Time Zones


Storing Vs. Displaying Dates
All dates are stored in UTC format with millisecond accuracy. However, dates are automatically converted to your time zone when displayed to you.

For example, a stored UTC date of 2022-01-01T00:00:00 may be displayed as 2022-01-01T05:00:00 if your time zone is 5 hours ahead of UTC. We automatically handle daylight savings when converting from stored UTC to your time zone for display purposes. 

Default Date Format
We uses the YYYY-MM-DD format by default. Often, we will display exact time stamps such as YYYY-MM-DDTHH:MM:SS format, but this behavior can be customized.

The YYYY-MM-DD format avoids confusion related to date interpretation. Indeed, a date such as 05/12/2022 is ambiguous. In Europe, it means December 05. In North America, it means May 12. A date of 2022-05-12 however has no ambiguity. In addition, alphabetic sorting always work when using the YYYY-MM-DD format (but not so using other formats).

Customizing Date Display
You can customize date settings by going to Account > My Profile.
  • Data Exports
    • Choose between a/ a specific date format vs. b/ your browser's date format.
  • Date Format
    • Toggle between various date formats (ex: YYYY-MM-DD, MM/DD/YYYY, DD-MM-YYYY).
  • Prefer Short Dates
    • Choose whether to hide timestamps when possible (ex: 2022-01-01T00:00:00 will be displayed as 2022-01-01).
  • Use Workspace Time Zone
    • Instead of displaying dates relative to your browser's time zone, we will display them relative to your workspace's time zone. This setting is very useful if you are viewing a workspace from a remote time zone, but want to see dates relative to your workspace's time zone.
 


Important Technical Note for Sales Cookie Support
When impersonating a customer account, you typically want to look at dates relative to the customer workspace's time zone. We recommend either changing your PC's time zone to match the workspace's time zone, or checking the "Use Workspace Time Zone" option above (on the impersonated customer account). This way, your own browser time zone does not come into play and you won't get confusing time offsets.

Understanding Workspace Time Zones
To calculate commissions, we need to determine which transactions "fall" within each calculation period. For example, consider a transaction dated 2022-03-31T23:59:34 UTC. This transaction date is towards the very end of March in UTC. Should the transaction be processed in March? Or should it be processed in April?

If our workspace's time zone in US Eastern, we must convert this transaction UTC date to US Eastern time, and determine whether it "falls" in March or April. In other words, your workspace's time zone determines exact cutoff dates for calculations. You can specify your workspace's time zone under Settings > Time Zone.



Interpreting Dates - CRM / Accounting Systems
When retrieving data from CRM or Accounting systems, dates are often returned in UTC by the API (ex: CreateDate, UpdateDate, etc.). Therefore, there is no ambiguity, and provided UTC dates can be stored as is without conversion. 

However, there are cases where CRM or Accounting systems return relative dates (ex: CloseDate). Those dates may be relative to a default time zone within the CRM or Accounting system, or relative to the user account used to retrieve the data. Relative dates are interpreted as being relative to your workspace's time zone. 

We recommend aligning your workspace's time zone with your CRM or Accounting system so relative dates are understood correctly. 

Interpreting Dates - CSV Files
When processing data from CSV files, dates can be ambiguous. 

How can we determine if dates specified in your CSV are a/ in UTC, b/ relative to your own time zone, or c/ relative to your workspace's time zone? Different approaches can be used to interpret dates correctly.

First, your CSV may specify dates in UTC using the YYYY-MM-DDTHH:MM:SSZ format. The Z indicator is an ISO convention. The Z indicator tells us that dates are UTC., Therefore, there is no ambiguity and those UTC dates can be stored as is without conversion. Of course, we will convert them to your time zone for display purposes, as explained above.

Second, if you do NOT specify dates as UTC, we assume that dates are relative to your workspace's time zone. For example, if you only specify "2022-01-01", and your workspace's time zone is US Eastern, we will assume this means 2022-01-01, US Eastern time. We will convert specified dates from US Eastern to UTC before storing them.

Third, you may not always specify dates in the YYYY-MM-DD format. For example, your CSV may have dates in the MM/DD/YYYY or DD-MM-YYYY format. If we encounter a date such as 05/12/2022, we must decide whether this means May 12 or December 05. By default, we will detect your browser's language, and determine whether it uses a "month first" vs. "day first" format. To avoid any ambiguity, specify your CSV date format in Settings > Time Zone. This setting is specific to CSV imports, and tells us how dates within your CSVs should be interpreted regardless of the browser used to upload CSVs.



Important Technical Note for Sales Cookie Support
When uploading CSV files on behalf of customers, and dates are ambiguous, your own browser locale will determine how to interpret them. For example, a US customer may have uploaded a CSV file, with dates specified in the "month first" format. However, when you upload the same CSV file from a European location, your browser has a "day first" interpretation. As a result, dates won't be processed correctly. Either use the option above (recommended), reformat CSV dates to match your own locale before upload (not recommended), or change your browser locale to match the customer's (undesirable).

Supported Date Formats
We support a wide variety of date formats. For example, "April 2022" is understood as "April 1st 2022", while "Q1 2022" as "January 1st 2022." We also support Excel-type numeric dates such as "43831.00", Unix timestamps such as "20190417", or even dates with offsets such as "2022-01-30T12:34:56-05:00". 

Some of those formats (for example, Unix timestamps such as  "20190417") are equivalent to UTC dates. Other ambiguous date formats will be interpreted as being relative to your workspace's time zone, as explained above.

Using Dates In Formulas
In formulas and custom variables, dates should be specified using the #YYYY-MM-DD# format. You can safely quote dates (ex: '#YYYY-MM-DD#' or "#YYYY-MM-DD#" are valid).

Some important rules:
  • Understand that dates read from [Transaction] records are always UTC dates
  • Understand when you are dealing with UTC dates vs. relative dates
  • Carefully choose between [Calculation].[start_date] and [Calculation].[start_date_utc] 
  • Only compare UTC dates to other UTC dates, and relative dates to other relative dates 

For example, suppose that you want to check if a transaction's [Closed Date] is after a calculation's quarter start date. This is what you would do:


 
First, we read the [Closed Date] from the [Transaction]. This is an UTC date because read from a [Transaction] record. Next, we get the [Calculation]'s [start_date]. We used [start_date] and not [start_date_utc]. This is a relative date (relative to your workspace's time zone). For example, when calculating February 2022 commissions, this will be 2022-02-01 (relative to your workspace's time zone).

Next, we get the quarter start date from this relative start date. Here too, the quarter start date is relative to your workspace's time zone. For example, this will be 2022-01-01 (start of Q1), relative to your workspace's time zone.

Finally, the tricky part. We must convert the relative quarter start date to UTC before comparing with the transaction's UTC date. For this reason, we use ToUtc(). 

Note that we could have taken a different route where we convert the [Transaction]'s date to a relative date, and compare relative dates instead. Both approaches are valid. The point here is that, to calculate the correct start of the quarter, we should work with relative dates. But when comparing, we need to ensure the comparison is valid.

    • Related Articles

    • Understanding Calculation Issues

      As you run calculations, we examine your calculation timeline to find potential problems, such as duplicate calculations or gaps between calculations. Some of those issues may be false positives. A purple bar will alert you any time potential issues ...

    • Error - Unable To Perform A Realistic Calculation Simulation Due To An Insufficient Time Span

      Applicability This error applies to the following situation: You are running a calculation using simulation You have either few transactions, or they are very "condensed" in time Impact This error prevents you from completing a simulation. ...

    • Why Are Transaction Dates Important When Importing Transaction Data?

      Let's say you upload a CSV file with records like this: As you can see, there is no date column. So, if you wanted to calculate January commissions, we would not know if those sales would apply since there is no effective date column we can use. In ...

    • What Does The "Credit Date" Represent?

      The "Credit Date" field tells you the effective date which allowed the transaction to fall within calculation period. When running a calculation for a given time period, we must determine which transactions are eligible based on their dates. In some ...

    • How Can I Enable Revenue Analytics For My Reps?

      Why Enable Revenue Analytics? Revenue Analytics provide a unique understanding of your organization's revenue and sales performance. Revenue Analytics help identify your best and worst customers. Using Revenue Analytics, you can identify top ...