Format Dates for CSV Files

read
Last updated at:

Overview

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

In this article, you will learn how to edit dates in Excel and Google files to match your desired date format for your account. This will make the process of uploading a CSV with customer data seamless.

When you import dates with timestamps, map this field to the date data type in Klaviyo. However, if you import a date without a timestamp, a default time of midnight UTC is applied if mapped to the date data type. This may cause date based flows to send a day early or late depending on the account’s timezone. For that reason, if you only upload dates (no timestamps), map them to the string data type instead.

Microsoft Excel

Edit for Consistency

To edit dates and timestamps within an Excel file that houses your data, 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).

excel_dates.png

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

excel_date_change.png

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

excel_dates_2.png

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.

UK_dates.png

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.

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.

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.

spreadsheet_settings.png

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.

Let’s say you are in the UK; set your Locale as United Kingdom, and save this change. After saving, your dates will reformat to reflect this change.

Ideally, edit Locale prior to reformatting for consistency. However, if you edit Locale second and then do not immediately see a change to your cells, simply select them again and click Format > Number > Date. Google Sheets will then update to reflect location changes.

Edit for Consistency

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

google_dates.png

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

format_date__google_.png

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

google_dates_2.png

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

123.png

Now that you have one consistent format across your spreadsheet, you will likewise want to make sure 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 choose to create your own custom format.

more_date_and_times.png

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

For example, you may choose 08/05/1930 which would be an acceptable format to use in Klaviyo.

Google_date_redo.png

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

google_updated_dates.png

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

x
Was this article helpful?
13 out of 17 found this helpful