Formatting dates for CSV files reference

You will learn

Learn how to edit dates in Excel and Google Sheets to match a supported date format in Klaviyo. This helps you avoid date-formatting errors when uploading a CSV with customer data.

When you run an ecommerce business that sells to customers around the world, many of these customers will format dates differently. Likewise, when you import dates from an Excel or Google Sheets file, these systems may reformat dates according to their default date settings. For example, Excel may reformat “March 1, 2020” to “1-Mar”.

Acceptable date and timestamp formats

For date-only values, use YYYY-MM-DD or MM/DD/YYYY. These formats use a 4-digit year to avoid ambiguity. Klaviyo reads slash-separated dates as month/day/year, regardless of your spreadsheet’s locale. For dates without a time, follow the Text mapping guidance above.

For timestamps, use a 24-hour clock. When mapped to Date, a timestamp without a timezone offset is interpreted as UTC. To specify another timezone, include its UTC offset, such as 2014-09-30 13:34:08-04:00. The offset +00:00 means UTC.

The following examples show supported timestamp formats. They illustrate different levels of precision and timezone choices, so they do not all represent the same instant:

• 2014-09-30 13:34:08
• 2014-09-30 13:34:08+00:00
• 09/30/2014 13:34:08
• 09/30/14 13:34:08
• 09/30/2014 13:34
• 09/30/14 13:34
• 2014-09-30T13:34:08
• 2014-09-30 13:34:08.000001
• 2014-09-30T13:34:08.000001
• 2014-09-30 13:34:08.000001-04:00
• 1412098448 (Unix)

Microsoft Excel

Edit for consistency

To edit dates and timestamps within an Excel file, first select the cells that you want to reformat. Press Command+1 or Control+1 (if you are using macOS) or CTRL+1 (if you are using Windows). For instructions for your version of Excel, see Microsoft’s date-formatting guide.

A Format Cells popup will appear. Select Number from the main tab (if it is not already selected), and under Category, select Date.

Under Type, choose your desired date format. Make sure that you choose a date and timestamp format that is supported within Klaviyo.

For example, if you select 2012-03-14 (which is supported in Klaviyo), confirm the format with OK to apply it to the selected cells.

Edit for location

Excel’s regional settings can affect its default date formats. For instructions for your version, see Microsoft’s guidance on date formats and regional settings.

A UK-style date uses day/month order, while Klaviyo reads slash-separated dates as month/day. For example, Klaviyo reads 05/08/2020 as May 8, 2020, not August 5. Before saving your CSV, format dates as YYYY-MM-DD or MM/DD/YYYY even if you use a UK locale.

Once your Excel file reflects the correct date/timestamp formatting, click File > Save As. Save your file as a CSV.

Then, head back to your Klaviyo account and upload your CSV file to a list.

Google Sheets

Edit for location

You can change dates in Google Sheets to reflect your native date format, or locale, as well. For example, if you are in the UK, you may want to change the US date formatting (MM/DD) to UK formatting (DD/MM).

To change your spreadsheet’s locale or time zone, follow Google’s instructions for spreadsheet settings.

Changing the locale affects the spreadsheet’s default date, number, and currency formats for everyone using the spreadsheet. It does not change the Google Sheets display language.

For example, a United Kingdom locale can display dates in day/month/year order. Before exporting the CSV for Klaviyo, apply YYYY-MM-DD or MM/DD/YYYY as described below.

Edit for consistency

To edit dates and timestamps for consistency within a Google spreadsheet, select the cells that you want to reformat.

Select Format > Number > Date (or Date time if you want to edit both the date and time in your file).

This will update your dates to reflect the default Google Sheets formatting, as shown in the image below.

Alternatively, you can find date information by selecting the 123 dropdown.

Now that you have one consistent format across your spreadsheet, make sure that it aligns with Klaviyo’s guidelines for formatting dates and timestamps.

Leading zeros in slash-separated months and days are optional: 8/5/1930 and 08/05/1930 both mean August 5, 1930 in Klaviyo. You can still use leading zeros for consistency. To change the format, follow Google’s custom date and time formatting instructions.

Choose YYYY-MM-DD or MM/DD/YYYY for date-only values. If your column includes a time, choose one of the supported timestamp formats above. Click Apply.

For example, 08/05/1930 is an acceptable date format in Klaviyo when it means August 5, 1930. To represent May 8, 1930, use 1930-05-08 or 05/08/1930.

This will update all of your dates to appear in your desired format which can then be uploaded into Klaviyo.

From there, navigate to File > Download, and download your Google Sheet as a CSV file. Next, you will be able to upload your CSV file to a Klaviyo list.

If these customers already exist in your account, when you upload your CSV to your list, new profiles will not be added. Instead, their profile will be updated to reflect the changes you make when you map fields. This will avoid duplicating customer information and cluttering your account.

CSV upload example

Below is an example of what this CSV file may look like as you upload it with the correct date/time formats.

Additional resources