Formatting Date, Time, and Numeric Values in Make
Format date, time, currency, and percentage values in Make before sending them to SpreadsheetWeb Hub with parseDate, formatDate, and parseNumber.
When a Make scenario sends data to SpreadsheetWeb Hub, the source value must match the data type and format expected by the target named range. Date, time, currency, and percentage values can otherwise arrive as text and cause parsing or Excel calculation errors.
This guide explains how to use Make's parseDate, formatDate, and parseNumber functions to normalize values before they reach a SpreadsheetWeb Hub application.
Format date and time values
Systems can represent the same date or time in different formats. Make can add or subtract time, convert time zones, and transform a source value into the format expected by SpreadsheetWeb Hub.
Automatic parsing
Make can handle many date and time values automatically when the item data type is set to date or time. Click a SpreadsheetWeb Hub named-range field in Make and check the icon displayed for that parameter.
If you see another icon such as text (A) or number (#), check the corresponding named range in your Excel file and change its number format to date or time.
Update and republish the application after changing the workbook. The correct data type will then appear in Make.
Resolve an invalid date error
An invalid date error means that Make cannot parse the date or time value received from the previous step.
Provide the source pattern with Make's parseDate function. The time-zone argument is optional, so the source value and its format are enough in most scenarios.
parseDate(text; format; [timezone])
| Argument | Value |
|---|---|
text | Source date or time value. |
format | Pattern describing the source value, such as DD.MM.YYYY hh:mm. |
timezone | Optional time zone used when parsing the value. |
For additional patterns and examples, see Make's date and time function documentation.
Resolve a #VALUE! error in Excel
Excel returns #VALUE! when a formula attempts to perform a mathematical operation on text.
If you do not want to change the Excel workbook, convert the source date or time into the required output format with Make's formatDate function.
formatDate(text; format; [timezone])
| Argument | Value |
|---|---|
text | Source date or time value. |
format | Pattern for the output value, such as DD.MM.YYYY hh:mm. |
timezone | Optional time zone used when formatting the value. |
For additional patterns and examples, see Make's date and time function documentation.
Format currency values
Make may treat a currency value as text when it includes a symbol such as $, EUR, or CHF. Sending that text to a numeric named range in a SpreadsheetWeb Hub application causes an invalid number error.
Use parseNumber to extract the numeric portion of the value.
parseNumber(number; decimal separator)
| Argument | Value |
|---|---|
number | Text or currency value containing the number to parse. |
decimal separator | Character that separates the integer and decimal portions of the number. |
After parsing the currency value, map the numeric output to the SpreadsheetWeb Hub module and run the scenario again.
Format hundred-based percentage values
Some Make modules send percentage values with a percent sign. This produces the same text-parsing problem as a currency symbol.
Apply parseNumber to remove the percent sign. The parsed value is then 100 times the decimal value expected by Excel, so divide the result by 100 with Make's division operator.
Important: Select the division operator from Make's Math functions tab. Typing a slash character directly does not create the same operation.
Before activating the scenario, test representative values for every expected source format, including empty values, time zones, decimal separators, and percentage symbols. This confirms that Make sends a valid value to the target SpreadsheetWeb Hub named range.