Dates and times in your underlying data sets can be represented in different ways. Some data sets clearly indicate that a particular field is a date or datetime. Other data sets might use text and numeric values that represent dates in ways that are easy for Looker Studio to recognize. In these cases, Looker Studio creates Date or Date & Time fields in your data source to handle that information.
Sometimes, though, the data is ambiguous, making it difficult for Looker Studio to know how to handle it. For example:
- Does 20201210 represent a date? Maybe, maybe not. It could represent a number or currency value: $20,201,210.
- Does 12/10/2020 represent Dec 10, 2020 or Oct 12, 2020?
Can't convert to date
If you connect to data that contains ambiguous dates or times, you may see a message saying "Looker Studio can't convert [field] to a date". To resolve this, do one of the following:
Change the underlying data
If you can edit the data set, consider changing the format of your date field to a full year, month, and day format. You may also be able to set the field's data type to date or date and time. This is the recommended approach, especially if you'll be creating multiple data sources from this data set.
Convert to date using a calculated field
To create a valid Date or Date & Time field from your original unrecognized field, create a new calculated field and use the PARSE_DATE or PARSE_DATETIME function. See the examples below, replacing field with the name of the original (unrecognized) field.
Example formulas
If field is a text field…
|
Format |
Formula |
|---|---|
|
2020-03-18 |
PARSE_DATE("%Y-%m-%d", field) |
|
2020/03/18 |
PARSE_DATE("%Y/%m/%d", field) |
|
20200318 |
PARSE_DATE("%Y%m%d", field) |
|
3/18/2020 |
PARSE_DATE("%m/%d/%Y", field) |
|
18/3/2020 |
PARSE_DATE("%d/%m/%Y", field) |
|
Mar 18, 2020 |
PARSE_DATE("%b %d, %Y", field) |
|
Wed, Mar 18, 2020 |
PARSE_DATE("%a, %b %d, %Y", field) |
|
March 18, 2020 |
PARSE_DATE("%B %d, %Y", field) |
|
Wednesday, March 18, 2020 |
PARSE_DATE("%A, %b %d, %Y", field) |
If field includes the time:
|
Format |
Formula |
|---|---|
|
2020-03-18 16:45:00.000000 |
PARSE_DATETIME("%Y-%m-%d %H:%M:%E*S", field) |
|
2020-03-18T16:45:00.000000 |
PARSE_DATETIME("%Y-%m-%dT%H:%M:%E*S", field) |
If field is a number field…
|
Format |
Formula |
|---|---|
|
20200318 |
PARSE_DATE("%Y%m%d", CAST(field AS TEXT)) |