Data Studio
2.6K members online now
2.6K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Converting a date to the correct format in Data Studio

[ Edited ]
Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

I am using Google Sheets as my data source for lead data imported from a Excel spreadsheet.  The date format in the Excel sheet is mm-dd-yy and it seems I need to covert the date to yyyymmdd in Google Data Studio.  I've tried to use the current format but GDS doesn't seem to recognize it.

 

If my above assumption is right, where is the best place to convert this date format?  Google Sheets or in Google Data Studio using a Calculated Field.  How exactly (which function) is this accomplished?

 

Thanks!

 

1 Expert replyverified_user

Converting a date to the correct format in Data Studio

Top Contributor Alumni
# 2
Top Contributor Alumni

HI

I am not sure what exactly your issue is but when I tried to do similar import GDS converted date format automatically. Here is my sample sheet (https://docs.google.com/spreadsheets/d/1BHQuerVdiZXFGU5NiR2U2ghiidnXtd9Dv0i3Nwrat3I/edit#gid=0)

Regards, Nik
LinkedIn  |   @nikhilparachure | My Blog

Converting a date to the correct format in Data Studio

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

I created a new sheet with just the date and the lead grade.  Here is the date format I used.

 

Lead Date Grade
04-25-16 FYI Only
06-20-16 Warm
08-12-16 Warm
10-06-16 Warm
04-25-16 FYI Only
11-22-16 Cold
11-18-16 FYI Only
10-31-16 Hot
09-12-16 FYI Only
09-02-16 Warm
07-21-16 Cold
08-12-16 FYI Only
04-11-16 FYI Only
04-25-16 Nurture
09-22-16 Hot
11-09-16 Hot
06-10-16 Hot
07-18-16 Hot
04-25-16 FYI Only
11-16-16 Hot

 

For some reason GDS doesn't recognize this format.  Shouldn't the date be converted to YYYYMMDD?  This is the date formate GDS is recommending. 

Converting a date to the correct format in Data Studio

Top Contributor Alumni
# 4
Top Contributor Alumni

Hi 
Okay I am not able to reproduce this scenario because whenever I try to create report without any metrics I get error "Invalid dimension or metric selected".

Regards, Nik
LinkedIn  |   @nikhilparachure | My Blog

Converting a date to the correct format in Data Studio

Follower ✭ ☆ ☆
# 5
Follower ✭ ☆ ☆

You can use the count function or any random metric.

Marked as Best Answer.
Solution
Accepted by topic author Tomi K
January 2017

Converting a date to the correct format in Data Studio

Google Employee
# 6
Google Employee

Hi Tomi

 

The "canonical" date format for Data Studio is YYYYMMDD. That's not to say you can't display your dates in sheets in some other format (e.g.mm-dd-yy). But in your Data Studio data source, make sure the Type field says Date (YYYYMMDD). That's the dimension you will use as your "Date Range Dimension." That's what Data Studio uses to set the time frame of the data displayed.

 

If you want to show your data in some different date unit, say, week, or quarter, you can create a new date dimension by duplicating the original one and setting it's date type accordingly in the data source. Does that make sense? 

 

Here's the Help Center article: https://support.google.com/360suite/datastudio/answer/6401549

 

Hope this helps!

 

Rick

Converting a date to the correct format in Data Studio

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆

Hi Rick,

 

Thank you for taking the time to explain this in detail.  It was a little frustrating at first but after I changed the format to YYYYMMDD in Google Sheets everything in Data Studio worked fine.  It was also easy to change to Year Month with a calculated field.  Again, very much appreciated!

 

Tomi

Converting a date to the correct format in Data Studio

[ Edited ]
Follower ✭ ☆ ☆
# 8
Follower ✭ ☆ ☆

FOR BIG QUERY....

 

So I discovered that the date sent to DataStudio needs to be a "YYYYMMDD" string.  Cast it to a string in your BigQuery view.

 

Why is this worth noting.  Leaving it as an INT or a DATE will work when ALL dates are selected.  However when you try to filter by a date it will error as DS is trying to do a STRING=[INT or DATE] comparison.

Re: Converting a date to the correct format in Data Studio

[ Edited ]
Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Hi Rick,

 

Thanks for the explanation. I am still unclear, for Google Sheet, which one of these would work:

 

(a) YYYYMMDD entered in "text" format. The cell shows 20170403, and the value is "20170403".

(b) YYYYMMDD entered in "date" format. The cell shows  20170403, but the value is 2017/04/03 

(c) Any date format would work, as long as the value is date format. E.g. 2017-04-03.

 

ScreenHunter_549 Apr. 03 19.21.jpg 

 

 

 

Likewise, what should I do if I want to show month (MM)? 

(a) Enter January as text

(b) Enter 01 as text

(c) Enter 1-Jan-2017, and use "MM" as Date Format

 

 

 

 

Thanks!

 

 

Converting a date to the correct format in Data Studio

Google Employee
# 10
Google Employee

Hi,

 

To work with dates in a Data Studio report (e.g., in the time series chart), there are 2 date-related dimensions to consider:

 

1) The Default Date Range dimension. This is your main Date field in your Sheet (or other data set). It should contain valid complete dates in YYYYMMDD format. In your data source, be sure this is set to a Date (YYYYMMDD) type. If it's Text, change it to Date (and if you get an error saying we can't recognize the format, ignore that. If you have actual date data, it should work).

 

2) The Time Dimension. This is the dimension that controls the granularity of the time series (i.e., the X-axis units). If you want to show months in the chart, use a dimension where the Type in the data source is Month (MM).

 

Note that these 2 things can be the same. If you want your charts to show all dates in your data, the Default Date Range and Time Dimension can be the same dimension. If you want a different granularity (again, e.g., Months), duplicate the Date dimension in your data source and set the type of the copied field to the units you want (e.g.,Month (MM)) 

 

Hope this helps!