Format SpreadsheetWeb Data with Zapier Formatter

Formatter by Zapier can transform data between a source application and a SpreadsheetWeb Hub action. Add a Formatter step when dates, currency strings, or percentage values do not match the format expected by your Excel-based application.

This guide covers four common tasks:

Convert a European date to a format SpreadsheetWeb recognizes.
Extract a number from a currency-formatted string before calculation.
Format a calculated number as currency for a later Zap step.
Convert between whole-number percentages and decimal values.

Format dates and times

SpreadsheetWeb calculations expect a recognizable date value. Use a Date / Time Formatter step when the source application supplies a different date order, a localized string, or a time zone that the calculation does not expect.

Convert a European date to a US or ISO-style date

If the source uses a European day-month-year date, the SpreadsheetWeb Hub action may return a calculation error or interpret the date incorrectly.

SpreadsheetWeb calculation error caused by an incompatible date format in Zapier

Use Formatter by Zapier to parse the incoming value and convert it before the SpreadsheetWeb Hub action runs.

Insert a Formatter by Zapier step after the source step and before the SpreadsheetWeb Hub action.

Formatter by Zapier step inserted before the SpreadsheetWeb Hub action

Select the Date / Time event.

Date and Time event selected in Formatter by Zapier

Select Format in the Transform list, and configure these fields:

Input: Map the source date field.
To Format: Select the required output format. A US date format or year-month-day format is typically suitable for SpreadsheetWeb applications.
To Timezone: Select an output time zone for date-time values. Leave it blank for date-only values.
From Format: Select the format that matches the source value.
From Timezone: Select the source time zone for date-time values. Leave it blank for date-only values.

Zapier date formatting fields for input format, output format, and time zones

Test the Formatter step and confirm that the output date has the expected format.

Successful date conversion test result in Formatter by Zapier

Edit the SpreadsheetWeb Hub action and map the Formatter output to the corresponding date input.

Formatted date output mapped to the SpreadsheetWeb Hub action

Retest the SpreadsheetWeb Hub action and verify the calculated results.

Successful SpreadsheetWeb Hub action test after date conversion

Convert other date formats

The same Date / Time event can parse and convert other date and time formats. Match From Format to the source value and To Format to the format required by the destination.

Additional date and time transforms available in Formatter by Zapier

Format currency values

A source application may provide currency as a formatted text value that includes a symbol, grouping separator, or localized decimal separator. If SpreadsheetWeb expects a number, sending that string can cause an Excel #VALUE! result.

SpreadsheetWeb value error caused by a currency-formatted input

Extract the numeric value before the calculation. If a later Zap step needs a formatted currency string, apply the desired currency format after the SpreadsheetWeb Hub action returns its result.

Extract a number from a currency string

Insert a Formatter by Zapier step after the source and before the SpreadsheetWeb Hub action.

Formatter step placed between the source data and SpreadsheetWeb Hub action

Select the Text event and continue.

Text event selected in Formatter by Zapier

Select Extract Number in the Transform list and map the source currency value to Input.

Extract Number transform configured with a source currency value

Test the step. The output should contain the number without a currency symbol.

Extract Number test result without the source currency symbol

Edit the SpreadsheetWeb Hub action and map the extracted number to its numeric input.

Extracted numeric value mapped to a SpreadsheetWeb Hub input

Retest the SpreadsheetWeb Hub action and confirm the calculation succeeds.

Successful SpreadsheetWeb Hub action test with the extracted number

Format a number as currency

Add another Formatter step after the SpreadsheetWeb Hub action when the next application needs a human-readable currency string rather than a raw number. Select Numbers as the Formatter event.

Numbers event selected for a Formatter step after SpreadsheetWeb Hub

Select Format Currency in the Transform list, and configure these fields:

Input: Map the numeric result from SpreadsheetWeb Hub.
Currency: Select the currency to apply.
Currency Locale: Select the locale used for separators and symbol placement.
Currency Format: Choose whether to display a symbol or currency code and whether to group thousands.

Format Currency transform fields for currency, locale, and display format

Test the step and verify that the output matches the format required by the next action.

Formatted currency output from the Zapier action test

Normalize percentage values

Applications may represent 25% as either 25 or 0.25. If the source and SpreadsheetWeb workbook use different conventions, the calculation can be off by a factor of 100. Use a Numbers Formatter step to divide by 100 when converting a whole-number percentage to a decimal, or multiply by 100 for the reverse conversion.

SpreadsheetWeb result affected by a percentage scale mismatch

Insert a Formatter by Zapier step after the source and before the SpreadsheetWeb Hub action.

Formatter step inserted before SpreadsheetWeb Hub for percentage conversion

Select the Numbers event and continue.

Numbers event selected to access Zapier math operations

Select Perform Math Operation in the Transform list.

Operation: Choose multiplication or division based on the required conversion.
Input: Map the source percentage value and enter 100 as the other operand.

Perform Math Operation configured to multiply or divide by 100

Continue and test the operation.

Test result for the Zapier percentage math operation

Edit the SpreadsheetWeb Hub action and map the adjusted Formatter output to the percentage input.

Adjusted percentage value mapped to the SpreadsheetWeb Hub action

Retest the SpreadsheetWeb Hub action and verify the calculation with a known percentage value.

Successful SpreadsheetWeb Hub calculation after percentage normalization

Keep each Formatter step immediately before or after the action whose data it transforms. Test the transformed value and the SpreadsheetWeb calculation separately so a formatting mismatch is easy to identify.