Being more precise about date formats

I've been working with Data Studio today and realised it interprets dates a bit more strictly than charts in Sheets. I thought I'd share what I did - and its reminder to pay more attention to managing dates.

Firstly, I had a simple table of dates and a value for each month. In Google sheets, I’d used a dd/mm/yyyy formatted date which I have displayed as, for example, Mar 16.

Date and value table
Date and value table

Linking this sheet to Data Studio, I found, not surprisingly, that the dates were displayed back as a full dd/mm/yy format and that some days and months were switched.

A simple reformat of the dates to yyyymm fixed this issue. This allowed Data Studio to interpret and display the dates correctly:

Barchart by month
Barchart by month

My second challenge was to convert a text date into a month and year date that Data Studio could interpret.

The text date was, for example, Dec 2017. The conversion steps were as follows:

Text to date
Text to date

Convert the text date (Dec 2017) to a datevalue [=DATEVALUE(A2)]
Extract the end of month value using eomonth [=eomonth(B2,0)]
Finally, format the date again to yyyymm.

This allowed Data Studio to interpret and display the data correctly.

Data Studio chart
Data Studio chart