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

Excerpt from spreadsheet with Column A showing dates eg Jun 15 Column B showing numbers

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:

Bar chart showing months, displayed, for example as 'Dec 16' on the x axis

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:

Spreadsheet with 3 columns:
A: Text date eg Dec 2017
B: Datevalue eg 43070
C: eomonth eg 201712

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