Help Center
Try for Free Login

Tips for Success

See more

Convert Dates to ISO Format

To trigger Workflows using Date-Based Custom Fields, store dates in ISO Format (YYYY-MM-DD). Once formatted, you will see an icon calendar icon appear next to the Custom Field in their Profile: 

Person Profile with arrow pointing to the calendar icon next to a date based custom field

 

In this article, we'll convert dates into ISO format so you can start using dynamic date-based triggers.

Use Google Sheets to Convert Dates to ISO Format (Recommended)

Step One: Edit Your CSV in Google Sheets

The quickest way to convert dates into ISO format is by using a CSV file.

highlighting a column in Google Sheets, navigating to the Format menu, selecting Number, choosing Custom Date and Time, and applying the ISO date format

For this method, you will need a CSV containing the email address and the non-ISO custom field. You can use your own CSV or perform a People Export in Drip. When exporting from Drip, you only need to maintain the email address and the custom field with the date stored.

Now you’re set to follow the instructions below:

  1. Open Google Sheets and under File > Open choose your CSV containing the dates to be reformatted to ISO
  2. Highlight the cells containing the dates you want to convert to ISO by clicking the column name

A highlighted column in Google Sheets containing non-ISO dates

  1. Click on the Format option in the toolbar
  2. Hover over Number in the dropdown menu
  3. Select Custom Date and Time:

Google sheet navigation to Format, then Number, then Custom Date and Time

  1. In the Custom date and time formats dialog, select the option displaying an ISO Format example (YYYY-MM-DD) from the options provided

Googlesheet Custom Date and Time formats window pointing to Apply with ISO format selected

  1. Click Apply to save the changes. The dates under that column will now be in ISO format

Highlighted column in Google sheets with ISO formatted dates

  1. Download the edited CSV:
    • Under File choose Download then Comma Separated Value (.csv)

alt 

Step Two: Import ISO Dates to Drip Via Bulk Operation

Now that you have a CSV with dates in ISO format, add them to the right Profiles with a Bulk Operation.

  1. Go to People > Bulk Operations
  2. Click + to start a new Bulk Operation
  3. Select Begin under Upload a list of people

Drip Bulk Operation page with arrow pointing to a plus sign to create a new operation and selecting Upload a list of people option

  1. Upload or drag and drop your CSV file
  2. The box to Treat the first row in your file as headers is turned ON by default

Bulk Operation Preview page for CsV upload pointing to the preview headers and Treat the first row in your file as headers checkbox checked

  1. Click Next
  2. Automated Actions (optional)
    • The CSV import will handle updating the Custom Field, so no Additonal Actions are needed
      1. Replacing: If you are using the same Custom Field name in the CSV, the value will be replaced with the updated one.
      2. Adding New: If you renamed the Custom Field to a new name, then a new Custom Field will be added to Profiles.
  3. Click Next > Perform Operation
  4. Click Done to exit the page and view the progress of the operation, or click Cancel this operation to cancel the operation

Simple Re-formatting: Using a Bulk Operation and Liquid

If you only need to update the characters in your date format, a Bulk Operation can quickly apply that edit with the use of Liquid coding. For more complicated edits, we recommend using a CSV file via the method above.

For example, if your dates are stored as 2025/01/31, you’ll need to convert the “/” symbols to “-”. Replacing the “/” symbols with “-” can be easily achieved with the {{replace}}  Liquid Filter and a Bulk Operation as outlined below:

  1. Go to People > Bulk Operations
  2. Click + to start a new Bulk Operation
  3. Select Begin under Choose a segment
  4. Under the People Filter, select 
    • Custom Fields > Person’s custom field > “custom_date” > is Set where “custom_date” refers to the custom field name you are looking to convert to ISO.

alt

  1. Click Next
  2. Under the Automation Actions choose Set a person’s profile or custom field value
    • After selecting the correct Custom Field name, the value will need to use the following Liquid statement: {{subscriber.custom_date" | replace: “/”, “-”}}
    • Remember to replace “custom_date” with the Custom Field name containing the date in YYYY/MM/DD format. The Liquid code will pull that value to then replace “/” with “-”

Set a person's profile or custom field and the field set to custom_date and the value set to liquid filter

  1. Click Next > Perform Operation
  2. Click Done to exit the page and view the progress of the operation, or click Cancel this operation to cancel the operation
  3. Check an example profile to view the updated version of the Custom Field. It should be in ISO format with the calendar icon icon next to it

Workflows Using Date-Based: Custom Field Triggers

Now that your Custom Fields are in ISO format, you’re ready to use them to trigger date-based Workflows. Install one of our Date-Based Workflow Templates below, or build your own. Our pre-built Workflows have Triggers already defined to work with your ecommerce platform and include recommended Action and Delay steps.