Formatting dates for CSV files reference

You will learn

Learn how to edit dates in Excel and Google files to match the required date format in Klaviyo. This will make the process of uploading a CSV with customer data seamless and help you avoid upload errors.

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 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

See the example below for information around formatting your date and timestamps in a CSV file. Note that, you must format dates as either YYYY-MM-DD or MM/DD/YYYY. If there's no time of day associated with your date, you can set it to midnight by using the HH:MM:SS value 00:00:00.

For example, the date September 30, 2014, at 1:34:08 pm should be formatted using one of the following supported formats:

• 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 a macOS) or CTRL+1 (if you are using Windows).

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) it will reformat each selected cell to reflect this formatting, as shown below.

Edit for location

You can also change date formatting by location, or locale, under Language (location) in the Format Cells popup.

In the above example, English (United States) is selected; thus, the dates will reflect US formatting (MM/DD). However, if you based in the UK, for example, and want dates to reflect the UK formatting (DD/MM), select English (United Kingdom) from the dropdown.

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 do so, head to File > Spreadsheet Settings. The following popup will appear.

Edit the Locale to reflect your location by selecting your country in the dropdown. You can also edit the Display language and Time zone as well. Then, click Save settings.

If you're in the UK, set your Locale as United Kingdom and save this change. After saving, your dates will reformat to reflect this change.

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.

In the example above, the date does not include 0s in the Month and Day listed which are needed when you upload dates in Klaviyo. If this is the case and you need to update the default format, select More Formats.

Then click More date and time formats. Alternatively, you can create your own custom format.

After selecting More data and time formats, a popup will appear showing your current date format. You can then choose from a variety of options. Select your desired format. Then, click Apply.

For example, you may choose 08/05/1930 which is an acceptable format in Klaviyo.

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.

Additional resources

• Acceptable date and timestamp formats for profile and event properties
• Profile properties reference