How to Convert Text to Dates in Google Data Studio

Working with dates can often be a frustrating experience when connecting to different data sources. There are endless variations of date formats, which can wreak havoc when you try to build a report. When a date format is not recognized, you usually end up with errors, null values, and empty charts, or worse your data gets associated with the incorrect dates.

In Google Data Studio, proper date fields are required for plotting time-series charts, showing comparisons to prior periods, and using the date range control. When connecting to a dataset, Data Studio will automatically attempt to identify date fields in the source. In cases where this fails however, you can also manually specify the date format using built-in functions.

In this post, we’ll go through how to ensure that your date fields are properly formatted, so that you can take full advantage of the date-related features of Data Studio.

Formatting Dates in Data Studio

The most efficient option for parsing dates is to let Data Studio do it for you! However, if this doesn’t work because your date format is not recognized, you can proceed to the subsequent options for manually specifying the format.

Create a new data source. Data Studio will automatically attempt to identify date fields (See the full list of supported date types). If a date type is identified, the field will show a date icon and the selected type. It’s a good idea to review the types selected for your date fields to ensure they are accurate.
If your date field shows as Text, you can try manually selecting the appropriate date type from the dropdown. This may work in some cases. Alternatively, you can proceed to the last option and explicitly convert the text to a date in a calculated field.

If the above options don’t work out, use the PARSE_DATE function in a calculated field to tell Data Studio how to interpret your date field as follows:

PARSE_DATE(format_string, text), where the format_string must be composed of the date format symbols below and text is your date field.
For example, if you have a field named Date Input with values formatted like 15/12/2020, you could use PARSE_DATE("%d/%m/%Y", Date Input).

Here is a list of supported symbols for common date parts that you can use to construct the format string. Refer to the documentation for PARSE_DATE for a comprehensive list of supported formats and limitations.

Date part	Example string	Symbol
Full month name	January	%B
Abbreviated month name	Jan	%b
Day of the month as a number (01-31)	01	%d
Month as a number (01-12)	01	%m
Full year	2021	%Y
Abbreviated year (00-99)	21	%y

Once you have formatted the date fields in your data source, the best way to test that they are working is by adding a time series chart to your report. If you have converted a text field to a date, you can also add a table to view the text and date values side by side.

Have you had any trouble working with dates in Data Studio? Let us know how we can help!

Complete list of posts in our 2020 Data Studio series:

1. Visualizing the COVID-19 Pandemic in Google Data Studio
2. Creating a Google Analytics Dashboard in One Click
3. Using Google Maps in Data Studio
4. Visualizing BigQuery Public Datasets in Data Studio
5. Measuring Web Vitals – Part 2: Monitoring in Data Studio
6. Using Parameters in Data Studio
7. How to Style Links in Data Studio
8. Using Filter Controls in Data Studio
9. Extracting a Theme from an Image in Data Studio
10. Building a Google Analytics 4 Dashboard in Data Studio
11. How to Convert Text to Dates in Google Data Studio (this post)
12. 12 Tips for Enhancing Your Tables in Data Studio