Date and time formats differ across Google Sheets, Microsoft Dataverse, Freshsales, and Pipedrive in how each tool accepts, stores, or displays forecast close dates.
Key takeaways
- Google Sheets will silently recalculate numeric dates which fall outside of valid month or day ranges, so a successful date creation does not validate the original inputs according to Google Docs Editors Help.
- Microsoft Dataverse documents that the unshown time value is 12:00 AM, depending on the Time zone adjustment, which requires distinguishing stored time values from visible entry formats according to Microsoft.
- Freshsales allows users to enter dates in the format dd/mm/yyyy, in the user’s time zone, establishing a specific entry convention distinct from other tools' documented behaviors according to Freshsales.
- Pipedrive states that time format will match your default time zone format, meaning time fields depend on the configured zone rather than a fixed universal standard according to Pipedrive.
Date and time formats: the documented differences
When preparing forecast close dates, distinguish these entry behaviors from the stored time values each tool maintains. The documented differences matter because a date entered in one format may be stored or interpreted differently in another system. Review the specific input behavior for each tool before comparing close-date data across platforms.
Compare only the behaviors documented in the help pages
Scope this comparison to the specific input behaviors and data type definitions found in the official help pages for each tool. Do not assume that export formats or storage structures are identical across these platforms, as the documentation addresses entry conventions and field definitions rather than unified data schemas. For Microsoft Dataverse, the documentation states that when creating a column, you select the column's data type according to Microsoft. This selection determines how the system interprets the value entered into that specific field.
The documentation for Dataverse focuses on the creation choice for column types, which is a distinct step from entering calendar dates or time values. When preparing forecast close dates, distinguish between the act of selecting a data type during column creation and the subsequent entry of date values. The help pages describe what happens when specific inputs are provided or when fields are configured, but they do not provide a single standard for how all tools handle time zones or date parsing.
Google Sheets recalculates some invalid date inputs
When preparing close dates, a successful date creation in Google Sheets does not confirm the original inputs were valid. The DATE function will silently recalculate numeric dates which fall outside of valid month or day ranges, according to Google Docs Editors Help. For example, DATE(1969,13,1), which specifies the illegal month 13, will create a date of 1/1/1970, according to Google Docs Editors Help. Similarly, DATE(1969,1,32), which specifies the non-existent 32nd day of January, will create a date of 2/1/1969, according to Google Docs Editors Help.
These silent conversions mean that a close date appearing in a spreadsheet might represent a different calendar day than the one originally entered. The function also handles decimal inputs by removing the fractional part. DATE will silently truncate decimal values input into the function, such as a month of 12.75 will be interpreted as 12, according to Google Docs Editors Help. This truncation occurs without error messages or warnings to the user.
To prevent forecast errors, inspect the specific month and day values entered into the DATE function before relying on the resulting date. Do not treat the presence of a valid date string as proof that the underlying numeric inputs were within standard calendar bounds. Check for out-of-range months or days that may have rolled over to adjacent months. Verify that decimal values were not present in the source data, as they will be truncated to whole numbers. This review step ensures that the close date used in your weekly forecast review matches the intended calendar day.
Remember that these rules are specific to the DATE function in Google Docs Editors. Other tools may handle invalid inputs differently. Always check the documentation for the specific tool you are using. Plan your data validation steps accordingly. Check your close date formulas now to see if any might have been silently recalculated.
Illustrative example of a typed year and month
One documented rule uses a typed year of 1900 as the year and adds a typed year of 0 to 1900. A typed month of 13 sits outside the 12 months of a year, and that kind of rule recalculates the month instead of keeping it. Write the year and month you typed next to the year and month the cell shows. Keep the close date for the weekly review only when both pairs match.
Freshsales date entry and Pipedrive time formats
The Freshsales entry convention is specific to the user's local zone. It differs from tools that document silent conversions or fixed stored values. When preparing forecast close dates, you must distinguish this user-zone entry from other documented behaviors.
Pipedrive documents time fields with specific entry and format rules. A specific time is chosen from a time drop-down, according to Pipedrive. The time format will match your default time zone format, according to Pipedrive. This dependency ties the displayed format to the user's default zone setting. The documentation describes time fields specifically. It does not establish how close-date fields parse calendar dates. Do not assume the time field rules apply to date fields.
For your weekly forecast review, check the field type before comparing values. If you use Freshsales, note the dd/mm/yyyy entry in the user's time zone. If you use Pipedrive, note the drop-down selection and the default time zone format match. Record these entry conventions separately from any stored time values. This prevents mixing up input formats with storage behaviors.
You can also review how Choose a deal field type: 4 systems and storage rules handles similar distinctions. Additionally, see Deal forecast months: date rules across 4 tools for related date handling. These resources provide further context on field types and date rules.
Microsoft Dataverse column types and hidden time
When preparing close-date data, distinguish calendar-date columns from duration fields in Microsoft Dataverse. The documentation for "Column types in Dataverse - Training | Microsoft Learn" specifies that a column data type is required at creation. For date and time columns, the unshown time value is 12:00 AM, depending on the Time zone adjustment, according to Microsoft. This hidden time component means the stored value includes a time element even when only the date is visible in the interface.
Duration fields operate under different entry constraints. In "Create and edit columns in Dataverse using Power Apps - Power Apps | Microsoft Learn," a user can select a predefined value from the list or type an integer value using the format: "x minutes," "x hours" or "x days," according to Microsoft. Hours and days can be entered using decimals, for example, "1.2 hours" or "1.5 days." Minute values entered must be expressible in whole minutes, according to Microsoft. This constraint applies specifically to minute-level entries within duration fields, not to calendar date fields.
Keep duration entry separate from calendar-date interpretation when exporting forecast data. A duration field stores a length of time, not a specific point on the calendar. The midnight time value in date columns depends on the Time zone adjustment setting, which affects how the stored timestamp aligns with local time zones. Do not treat duration values as opportunity close dates; they represent elapsed time, not calendar points.
Tool differences reference table and conditional fit
The table below lists the four tools and their documented behaviors.
| Publisher | Documented Input Behavior | Stored Time or Format Rule | Forecast Close-Date Consideration |
|---|---|---|---|
| Google Sheets | Recalculates out-of-range months and days; truncates decimals | Years 0–1899 add to 1900; 1900–9999 use as-is | Inspect inputs, as successful creation is not validation |
| Microsoft Dataverse | Duration minutes must be whole; hours/days allow decimals | Unshown time is 12:00 AM depending on time zone | Keep duration entry separate from calendar dates |
| Freshsales | Users enter dates in dd/mm/yyyy format | Entry is in the user’s time zone | Distinguish entry convention from stored values |
| Pipedrive | Specific time chosen from a drop-down | Format matches default time zone format | Evidence describes time fields, not close-date parsing |
Google Docs Editors Help states that DATE will silently recalculate numeric dates which fall outside of valid month or day ranges. The same source notes that for years 0 to 1899, Google Sheets adds that value to 1900 to calculate the year.
Recommendation: When preparing data, check if a tool silently alters input. For example, if you enter a month of 13 in a spreadsheet, the tool may create a date in the following year rather than flagging an error. This behavior is documented in the Google Sheets source. Do not assume that a valid-looking output date proves the original input was correct.
Recommendation: Separate duration fields from calendar date fields. Microsoft Dataverse duration entry requires minute values to be expressible in whole minutes, which is a different constraint than calendar date storage. Mixing these types can obscure whether a value represents a point in time or a length of time.
Recommendation: Verify the time zone setting for any tool where the format depends on a default zone. Pipedrive time fields match the default time zone format. Freshsales entry is tied to the user’s time zone.
Inspect the field type and its documented storage and display behavior.
Inspect one close-date value used in your forecast preparation and record its tool, field type, entry convention, and any applicable time-zone setting.
FAQ: preparing forecast close dates
How do date and time formats differ across Google Sheets, Dataverse, Freshsales, and Pipedrive?
The four tools differ in how a close date is entered, stored, or displayed. Google Sheets DATE silently recalculates numeric dates falling outside valid month or day ranges, according to Google Docs Editors Help. Microsoft Dataverse includes an unshown time value of 12:00 AM that depends on the Time zone adjustment, according to Microsoft. Freshsales allows users to enter dates as dd/mm/yyyy, in the user’s time zone, according to Freshsales. Pipedrive states that the time format will match your default time zone format, according to Pipedrive.
Does the Freshsales date-entry convention apply to my user's time zone?
Yes, Freshsales documents that users can enter dates in the format dd/mm/yyyy of the user’s time zone, according to Freshsales.
What happens when Google Sheets DATE receives an out-of-range month or day?
The function silently recalculates these inputs rather than returning an error, according to Google Docs Editors Help. For example, DATE(1969,13,1) creates a date of 1/1/1970, while DATE(1969,1,32) creates a date of 2/1/1969, according to Google Docs Editors Help.
Can a Microsoft Dataverse time value be present without being shown?
Yes, the unshown time value is 12:00 AM, depending on the Time zone adjustment, according to Microsoft. This hidden value exists within the column type structure.
Which setting determines the documented Pipedrive time format?
The default time zone format sets the documented Pipedrive time format, according to Pipedrive.