Date blocks are essential tools for manipulating and formatting dates and times within a workbook. These blocks allow for various date and time operations, including conversion, formatting, and calculation. By using date blocks, users can perform complex date manipulations and ensure accurate time-based data processing within their applications.
Examples
Select date range and update drill through dimension
Update date variables
Date blocks
Convert date(time) [Auto|ISO|Epoch|Excel] [Date] to [Milliseconds (Epoch)|Excel]
The Convert date(time) [Auto|ISO|Epoch|Excel] [Date] to [Milliseconds (Epoch)|Excel] block converts a given date and time into either milliseconds since the Unix Epoch (January 1, 1970) or an Excel date serial number. Outputs a number.
Configuration
Inputs | Description |
Input type | Specifies the format of the input date. Options include Auto, ISO, Epoch, or Excel. Setting it to Auto allows for automatic detection of the date format. |
Date(time) | The date, datetime, or time value to be converted. This can be in any of the specified input formats as numbers or strings. |
Output type | Specifies the desired output format. Options include Milliseconds (Epoch) or Excel. This determines whether the converted date will be in milliseconds since the Unix Epoch (e.g., 1719321878802) or in the Excel date serial number format (e.g., 45468.64238). |
This block is useful for converting date and time values into formats suitable for time-based calculations and comparisons. The conversion to milliseconds (Epoch) is particularly useful for time-based calculations and comparisons, while the conversion to Excel format is ideal for compatibility with Excel-based applications and spreadsheets.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Format date(time) [Auto|ISO|Epoch|Excel] [Date] to [Format]
The Format date(time) [Auto|ISO|Epoch|Excel] [Date] to [Format] block formats a given date and time into the specified date format. Outputs a string.
Configuration
Inputs | Description |
Input type | Specifies the format of the input date. Options include Auto, ISO, Epoch, or Excel. Setting it to Auto allows for automatic detection of the date format. |
Date(time) | The date, datetime, or time value to be formatted. This can be in any of the specified input formats as numbers or strings. |
Format | Allows the selection of pre-defined format patterns or the Custom option to input a custom format pattern. |
Custom format pattern | Provide custom format pattern to format the input date. See list of all available formats. |
List of all available formats
Format | Output | Description |
| 24 | Two-digit year |
| 2024 | Four-digit year |
| 1-12 | The month, beginning at 1 |
| 01-12 | The month, 2-digits |
| Jan-Dec | The abbreviated month name |
| January-December | The full month name |
| 1-31 | The day of the month |
| 01-31 | The day of the month, 2-digits |
| 0-6 | The day of the week, with Sunday as 0 |
| Su-Sa | The min name of the day of the week |
| Sun-Sat | The short name of the day of the week |
| Sunday-Saturday | The name of the day of the week |
| 0-23 | The hour |
| 00-23 | The hour, 2-digits |
| 1-12 | The hour, 12-hour clock |
| 01-12 | The hour, 12-hour clock, 2-digits |
| 0-59 | The minute |
| 00-59 | The minute, 2-digits |
| 0-59 | The second |
| 00-59 | The second, 2-digits |
| 000-999 | The millisecond, 3-digits |
| +05:00 | The offset from UTC, ±HH:mm |
| +0500 | The offset from UTC, ±HHmm |
| AM PM | AM or PM |
| am pm | am or pm |
This block is useful for standardizing date formats within a workbook for consistency and ease of use.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Get [Year|Month|...] from date(time) [Auto|ISO|Epoch|Excel] [Date]
The Get [Year|Month|...] from date(time) [Auto|ISO|Epoch|Excel] [Date] block extracts the date/time component from a given date and time. Outputs a number.
Configuration
Inputs | Description |
Component | Options include Year, Quarter, Month, Day, Hour, Minute, Seconds, or Milliseconds. |
Input type | Specifies the format of the input date. Options include Auto, ISO, Epoch, or Excel. Setting it to Auto allows for automatic detection of the date format. |
Date(time) | The date, datetime, or time value to extract the date component from. This can be in any of the specified input formats as numbers or strings. |
This block is useful for isolating a date component from a date for reporting or calculation purposes.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Get [Start|End] of [Year|Month|...] from date(time) [Auto|ISO|Epoch|Excel] [Date]
The Get [Start|End] of [Year|Month|...] from date(time) [Auto|ISO|Epoch|Excel] [Date] block retrieves the start or end date time of the year from a given date and time. Outputs milliseconds since the Unix Epoch (January 1, 1970) number.
Configuration
Inputs | Description |
Start/End | Options are Start or End. |
Component | Options include Year, Quarter, Month, and Day |
Input type | Specifies the format of the input date. Options include Auto, ISO, Epoch, or Excel. Setting it to Auto allows for automatic detection of the date format. |
Date(time) | The date, datetime, or time value to extract the start or end of date component. This can be in any of the specified input formats as numbers or strings. |
This block is useful for calculating the beginning or end of the specified date component for a given date, which can be used in time-based analyses.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Add [Years|Months|...] [Number of] to date(time) [Auto|ISO|Epoch|Excel] [Date]
The Add [Years|Months|...] [Number of] to date(time) [Auto|ISO|Epoch|Excel] [Date] block adds a specified number of the specified date component to a given date and time. Outputs milliseconds since the Unix Epoch (January 1, 1970) number.
Configuration
Inputs | Description |
Component | Options include Year, Quarter, Month, Day, Hour, Minute |
Number of | Number input, supports both negative and positive values. When decimal values are passed for days and weeks, they are rounded to the nearest integer before adding. |
Input type | Specifies the format of the input date. Options include Auto, ISO, Epoch, or Excel. Setting it to Auto allows for automatic detection of the date format. |
Date(time) | The date, datetime, or time value to offset. This can be in any of the specified input formats as numbers or strings. |
This block is useful for date calculations involving date component offsets. Works for both negative and positive offsets.
When decimal values are passed for days and weeks, they are rounded to the nearest integer before adding.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Example
To convert a date time to local timezone of the browser, the Add [Years|Months|...] [Number of] to date(time) [Auto|ISO|Epoch|Excel] [Date] block can be combined with the Get timezone offset (min) block
Create Date using [Year] [Month] [Day]
The Create Date using [Year] [Month] [Day] block creates a date using the specified year, month, and day. Outputs milliseconds since the Unix Epoch (January 1, 1970) number.
Configuration
Inputs | Description |
Year | The year component of the date. |
Month | The month component of the date. |
Day | The day component of the date. |
This block is useful for constructing specific dates from individual year, month, and day components.
Combine it with Create Time using [Hour] [Minute] [Second] [Millisecond] by adding them together to create a date time.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Create Time using [Hour] [Minute] [Second] [Millisecond]
The Create Time using [Hour] [Minute] [Second] [Millisecond] block creates a time using the specified hour, minute, second, and millisecond. Outputs milliseconds since the Unix Epoch (January 1, 1970) number.
Configuration
Inputs | Description |
Hour | The hour component of the time. |
Minute | The minute component of the time. |
Second | The second component of the time. |
Millisecond | The millisecond component of the time. |
This block is useful for constructing specific times from individual hour, minute, second, and millisecond components.
Combine it with Create Date using [Year] [Month] [Day] by adding them together to create a date time.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
Get timezone offset (min)
The Get timezone offset (min) block retrieves the current browser timezone offset in minutes. This block is useful for obtaining the current timezone offset to be used in time calculations.
Supports
Feature | Description |
Embeds | Yes |
Reporting | Yes |
These date blocks are essential for managing and manipulating date and time values within a workbook. They enable various operations such as conversion, formatting, and calculation, providing users with robust tools for handling time-based data. Each block is supported within the workbook context and can be used to enhance the functionality and accuracy of time-related workflows.