Formatting Currency and Dates

When new orders arrive, the raw data can often be messy or inconsistently formatted. Standardizing currency amounts, dates, and numerical values ensures your records are accurate and ready for downstream processing, reporting, or invoicing.

flowchart LR
    A["Raw Order Data"] --> B{"Data Formatter"}
    B --> C["Standardized Currency (USD)"]
    B --> D["Converted Dates (EST)"]
    B --> E["Clean Text & Numbers"]

Formatting Currency

Incoming orders typically include a Subtotal, Sales Tax, and Total. To ensure these values are processed correctly by accounting systems, they should all be standardized to the same currency locale and decimal format.

1
Select the target fields
Identify the financial fields in your incoming order payload (e.g., SubTotal, SalesTax, and Total).
2
Apply the currency formatter
Set the transformation type to Format Currency for each field.
3
Configure the format settings
Apply the following standardized parameters to all three fields:
- Currency Locale: en_US
- Currency Format: ###0.00
- Currency: USD

Using the ###0.00 format ensures that your values always have exactly two decimal places (e.g., 1234.50 instead of 1234.5), which prevents rounding errors in most billing software.

Standardizing Dates and Times

Order systems often generate timestamps in UTC (Coordinated Universal Time), which can cause confusion if your team operates in a different timezone. You can automatically convert these dates and perform scheduling calculations.

Converting Timezones

To make order dates readable for your team, convert the raw UTC timestamp into a standard calendar date in your local timezone.

Setting	Original Value	Target Value
Timezone	`UTC`	`US/Eastern`
Format	`YYYY-MM-DDTHH:mm:ssZ`	`YYYY-MM-DD`

This transformation strips the time element and leaves a clean date (e.g., 2023-10-25), which is ideal for daily reporting.

Scheduling Checks (Date Math)

If you need to calculate a follow-up date or a scheduling check, you can manipulate the current date. Use the Add/Subtract Time transformation and set the expression to +1 day. This will automatically output tomorrow's date in the YYYY-MM-DD format.

Handling Special Number Fields

Sometimes, incoming orders include custom fields (like Square Footage or measurements) that might be blank or contain invalid data. You can use a combination of extraction and spreadsheet formulas to clean this up.

Extract the Number: Use the Extract Number tool to pull only the numerical digits from a messy text string (e.g., extracting 1500 from "1,500 sq ft").
Apply a Fallback Formula: If a critical field is missing, you can use a spreadsheet-style formula to provide a default value.

For example, to ensure a measurement field is never zero or blank, use this formula:

IF(OR("{{SqFt}}"="", "{{SqFt}}"="0"), 1, {{SqFt}})

This formula checks if the value is empty or zero. If it is, it defaults to 1. Otherwise, it keeps the original extracted number.

Advanced Data Cleanup

If your order data is scraped from emails or web forms, it might contain unwanted HTML tags or special characters that break URLs.

Remove HTML: Use the Remove HTML Tags transformation to strip out formatting (like <b> or ``) from line items.
URL Encoding: If you need to pass data into a web link, use the Replace tool to swap the ampersand character (&) with its URL-safe equivalent (%26).

When dealing with multiple products in a single order, you can convert array line items into a single readable text block. Use the Line-item to Text transformation and choose a separator (like a comma , or a newline [:newline:]) to cleanly list out the items.

Need to map custom fields?

Learn how to map custom lookup tables (like Referral Sources) to your standardized order outputs.