Date conversion

[sarah_hussain-1]

I have a date field in google sheets that is formatted as a date fiels YYYY-MM-DD. I download this as xlsx to then use as a data source in Studio Pro. This date field surfaces as a 6 digit number- what formula can I use to convert to date?

I tried

PARSE_DATE("%y%m%d", CAST(Month AS TEXT) )

But this gives me an error. Could someone supply the correct formula? 

Thanks

[blue1]

It would help if you could post an example of a date in Google Sheet and the corresponding 6 digit number in the Excel file. Screenshots would help, too.

[sarah_hussain-1]

Hi there. Sure, let me paste below.

Here is the date in the google sheet: 

And here is the month field in a chart

It is a number field

Does that clarify my problem?

[blue1]

Could you also share this column in the excel file you download from google sheets?

When download a sheet containing a date that is formatted like yours as an Excel file, there is no change in formatting:

[阿泰]

Hello, you need to change the date format to 'Text' first in Looker Studio. Please check the screenshot below for reference.

Then, enter the format PARSE_DATE("%y%m%d", date) in Looker Studio, and it will be done!

[sarah_hussain-1]

Hi, thank you for your suggestion. Unfortunately, that didn't work.

I'm using this data: https://www.heathrow.com/content/dam/heathrow/web/common/documents/company/investor/reports-and-pres...

I changed the month column to a text column but I still get an error!

I'm baffled

[blue1]

Could you click on "See details" under the system error and post that here?

[阿泰]

This is my raw data in Google sheet. I think the problem is we don't have same raw data format.

[阿泰]

Hi, in my Looker Studio, the custom field is in 'date' format. Therefore, you need to change the field format to 'date'. Please check the screenshot for reference

[sarah_hussain-1]

Hi! I tried to change it from number to date but I got this error. I also tried a formula in LS itself but then I get an error to say it's a unsupported field type- this function supports integers. But I cannot change it from Integer to date. So I'm very confused

[阿泰]

I used this formula to create a new column in your Excel sheet, imported it to your LS, and it worked. Please check the screenshot below.

[sarah_hussain-1]

I tried your formula and it worked in Excel:

But then When I uploaded to Studio it still surfaces as a number :

Can we utilise your formula directly as a calculated field in Studio? I tried DATETIME_ADD(1900-01-01, INTERVAL Month DAY) but it gives me this error:

Invalid formula - Function "DATETIME_ADD" doesn't support DATETIME_ADD(NUMBER, INTERVAL NUMBER part). Function "DATETIME_ADD" supports DATETIME_ADD(DATE, INTERVAL NUMBER part), DATETIME_ADD(DATETIME, INTERVAL NUMBER part).

[阿泰]

Yes, when you create a new column in an Excel file, just import it directly into Looker Studio. Also, remember to check if the new column (new month) is set to the "date" type. I checked your screenshot, and yours is set to "string," which I believe is one of the main reasons for the issue.

Here are my steps:

1. Add a new column in the Excel file using the formula: =A2 + DATE(1900,1,1) - 2

2. Import the file into Looker Studio

3. Verify that the column is correctly set to the "date" type

4. Create your report, and you're done!