Search code examples
datetimelooker-studio

Why is the Date & Time Data Type Menu Disabled in Google Data Studio?

After connecting a data set to Google Data Studio I tried to update types of field of data source and notice that several options of date & time data type is disabled:

data_source_date_text

I couldn't find any method to enable it.

This is the Excel (CSV) data set used (sample Google Sheets data):

Date Shirt Pants Other
1/1/1954 37 18 38
1/2/1955 34 25 14
1/3/1956 37 12 10
1/3/1957 38 20 23
1/4/1958 37 19 18
1/5/1959 33 30 37
1/6/1960 13 36 10
1/6/1961 15 29 29
1/7/1962 32 14 12
1/8/1963 21 36 33
1/9/1964 15 12 15
1/9/1965 11 24 13
1/10/1966 29 15 11
1/11/1967 31 30 20
1/12/1968 14 12 18
1/12/1969 37 13 14
1/13/1970 40 34 25
1/14/1971 36 17 35
1/15/1972 17 36 39
1/15/1973 29 29 34
1/16/1974 30 39 33

This is how Google Data Studio shows the relevant data:

data_studio_output

Google Data Studio report

Solution

  • The Date field is recognised as a text data type ("Zero or more letters, numbers, characters, or symbols"), thus the PARSE_DATE function ("Converts text to a date") can be used to create a Date field that is recognised by Google Data Studio, in the date, data type ("An actual calendar date, a calendar date with time, or an abstract date or time index. This will be formatted according to the locale"):

    PARSE_DATE("%m/%d/%Y", Date)

    The calculated field above uses the format elements %m ("month as a decimal number (01-12)"), %d ("day of the month as a decimal number (01-31)") and %Y ("year with century as a decimal number"), with the separator / between each of the elements, thus %m/%d/%Y parses 1/16/1974.

    Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

    gif