Calculated field - concatenate and preserve date format

[kjakstas]

Hi there,

I could use help with what I hoped was a simple calculated field. My source data has 4 separate End Date columns. In Looker Studio, I need a single End Date field. (Ideally my source data would spit out just one column, but alas). The End Date formatting is MM/DD/YYYY HH:MM

I created a calculated field CONCAT (End Date 1, End Date 2, End Date 3, End Date 4) which worked fine, except it converts the value to Text. I am unable to change it to Date (although I can change it to Number). 

I tried keeping that Text/Number calculated field, and creating a second formula field using PARSE_DATETIME and other variations, but am not having any luck.

Any advice? Thank you in advance!

[a_shah]

Hi @kjakstas 

Below is the syntax for parsing text to date and a few thoughts

1. using concat like you have mentioned will just glue all text together, try adding spaces and correct ordering as per the syntax above. - Link

2. If you have access to database then better to do this at database level and use the SQL to pull data into Looker Studio.

3. Create a view in database with such data cleanup done in it and then use it in the Looker studio.

Hope this helps

~Ashish