Google sheet date format

vaco
vaco Member
in Connectors

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

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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?

Answers

  • ArborRose
    ArborRose Coach 
    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)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • vaco
    vaco Member

    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
    MichelleH Coach
    Answer ✓

    @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
    vaco Member

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

Categories