Google sheet date format

vaco · 2024-05-20T17:32:59+00:00

There was an error rendering this rich post.

Hi, I have a Google sheet with a date column where I am using the googlefinance function. When I import the data into Domo using the Google sheet connector, the date changes from "4/20/2024 23:58:00" to "45,402.99861111111" in Domo. Please do you know how to fix this date problem? Thanks

Find more posts tagged with

Accepted answers

MichelleH

@vaco I have not had that issue with the Google Sheets connector before. Do you have data validation rules set up on your date field in Google Sheets to ensure it is all stored as a datetime?

All comments

ArborRose

When importing data into Domo, it might not recognize it as a date and treats it as a plain number.  The result you see as that big number instead of a date. You can try converting the numeric back into a data format. Or try changing it at the Google sheet.


DATE_ADD('1899-12-30', INTERVAL NumericDate DAY)

vaco

Thanks @ArborRose, I tried your formula in magic etl using the add formula tile but it didn't work. Now "45,402.99861111111" is "P45403D". Is there another formula to convert back "45,402.99861111111" into "4/20/2024 23:58:00" in domo?

MichelleH

@vaco I have not had that issue with the Google Sheets connector before. Do you have data validation rules set up on your date field in Google Sheets to ensure it is all stored as a datetime?

vaco

Thanks @MichelleH! It worked after making the changes in Google sheet.